How to Create a Pivot Table in Looker

Creating a pivot table in Looker is one of the most effective ways to transform a massive, dense table of data into a clear, summarized report. It allows you to flip rows into columns to cross-reference different dimensions and uncover insights that are difficult to see in a standard table. This guide will walk you through exactly how to set up, build, and refine pivot tables in Looker, using simple, clear examples.

First, What Is a Pivot Table, Anyway?

Imagine you have a long list of every single sale your company made last year. Each row contains the date, the customer, the product category, the region, and the sale amount. It’s useful, but it’s a lot to scroll through. Answering a question like "Which product category sold best in the East region?" would require a lot of manual filtering and summing.

A pivot table solves this. It lets you take one of those data points - like "Region" - and turn its values ("North," "South," "East," "West") into the columns of your report. You can then keep "Product Category" as your rows and place the total "Sale Amount" in the cells where they intersect.

Suddenly, that big, unwieldy list becomes a compact, easy-to-read summary that directly answers your question. You’re simply rearranging, or pivoting, the data you already have to get a more insightful view.

The Foundation: Setting Up Your Explore

Before you can create a pivot table in Looker, you need to build the initial data query in an Explore. The success of your pivot table depends entirely on the data you bring into this foundational view. Think of this as gathering all the right ingredients before you start cooking.

Dimensions vs. Measures: The Building Blocks

In Looker, every field is categorized as either a dimension or a measure. Understanding this distinction is VITAL for building any report, especially a pivot table.

• Dimensions: These are the descriptive attributes of your data. Think of them as the "who, what, where, and when." Good examples are Order Date, Product Category, Region, or User Name. Dimensions are typically the fields you group by.

• Measures: These are the quantitative, numerical values you want to calculate. They are the things you can do math on, like Sum of Revenue, Count of Orders, or Average Session Duration.

For a pivot table, you'll generally use dimensions for your rows and columns, and a measure for the values that fill the cells.

Start with a Simple Question

The best way to start is by formulating a clear question. Let’s stick with our example:

"What was our total sales revenue for each product category, broken down by sales region, for the last quarter?"

Breaking this down, we know we need:

• Dimension 1 (for rows): Product Category

• Dimension 2 (to pivot into columns): Region

• The Value (measure): Total Sales Revenue

• The Filter: Date is Last Quarter

With this plan, we can move into the Looker Explore and start building.

Step-by-Step: Creating Your First Pivot Table in Looker

Now for the fun part. Let's walk through creating the pivot table based on our question above. The process is surprisingly straightforward once you get the hang of it.

Step 1: Build Your Base Report

First, ignore the pivot for a moment and just build a flat table containing all the data you need.

1. Navigate to your desired Explore in Looker.

2. In the field picker on the left, select your dimensions: Product Category and Region.

3. Select your measure: Total Sales.

4. Add a filter for the time period. Find your desired date dimension, filter on it, and set it to "is in the past 1 complete quarter."

5. Click Run.

At this point, your results will look like a standard, long-form data table. Something like this:

This data is correct, but not very scannable. We’re ready to pivot.

Step 2: Choose Your Pivot Dimension

This is the key step. We need to tell Looker which dimension we want to turn into our columns. In our case, it's Region.

• In the Data section (where you selected your fields), find the Region dimension. Hover over it and click the gear icon that appears on the right.

• From the dropdown menu, select Pivot.

You'll see the Region dimension move up to a new section in the Data bar called "Pivoted." This confirms that this field will now form your columns.

Step 3: Run the Pivoted Report

With Region now selected as the pivot, simply click Run again.

Looker will re-process the query. Instead of a long table, you’ll now get a clean, wide-format table where each unique value from the Region field is its own column:

And just like that, you have a pivot table! The report is now much easier to interpret, a supervisor can quickly scan down the columns to compare regional performance, and you've successfully transformed your raw data into a valuable business insight.

Advanced Tips for Better Pivot Tables

Once you’ve mastered the basics, there are a few features you can use to make your pivot tables even more powerful.

Pivoting by Multiple Dimensions

You’re not limited to a single pivot. Let's say you want to see sales not just by region, but also by quarter within each region. You can pivot by both Region and Order Quarter. Looker will create grouped columns, with each quarter nested under each region, giving you an even more granular analysis.

Controlling Overly-Wide Tables

What if you pivot by a dimension that has dozens of unique values, like "City"? Your table would become comically wide and impossible to read. Looker has two features to control this:

• Column Limit: You can set a limit on the number of pivot columns displayed (e.g., 20). Looker will show the top 20 by your sorting criteria and group the rest into an "Other" category.

• Row Limit: Similarly, if you have too many rows, you can limit them to focus on the most important items.

Using Totals and Subtotals

Pivot tables become much more useful when you can see summaries. In the Data section, you can check boxes for Row Totals and Totals (which are column totals). This adds a high-level summary view so you don't have to do the math in your head. It's a simple click that adds immense value to your report.

Visualizing Pivoted Data

Pivoted data is the perfect input for certain chart types, especially stacked column charts. By putting Product Category on the X-axis and Total Sales on the Y-axis, your pivoted "Region" fields can automatically be used to create the stacked segments within each bar. This provides a great visual for comparing both the total sales per category and the regional contribution to each of those totals.

Common Pivot Table Gotchas (And How to Fix Them)

As with any powerful tool, it's easy to make a few common mistakes when you're starting out. Here's what to watch out for.

• The Mistake: Pivoting a high-cardinality dimension.

Cardinality just means the number of unique values. Don't pivot by something like User ID, Order ID, or even Zip Code. You’ll generate thousands of columns and either crash your browser or make your report completely unusable.The Fix: Only pivot by discrete categories with a limited number of values, like region, status, device type, or category.

• The Mistake: Not filtering your data first.

If you try to create a pivot table on five years of data without any filters, the query can be very slow and the resulting table might be overwhelmingly large.The Fix: Always start by narrowing your scope with filters. Use a specific date range, filter for a particular team, or focus on a single product line first. You can always expand later.

• The Mistake: Forgetting about measures.

A pivot table is meaningless without a numerical measure in the cells. If you only select dimensions, your table will be empty of valuable data.The Fix: Always include at least one measure (like a sum, count, or average) to populate the values in your pivoted report.

Final Thoughts

Pivot tables are a fundamental skill for anyone doing data analysis in Looker. They may seem complex at first, but the core idea is simple: turn the values of one dimension into the columns of your report to create a clear, multi-dimensional summary. By starting with a question, building a standard table, and then applying a pivot, you can turn messy data into a powerful tool for making decisions.

Of course, mastering the specifics of Looker - navigating the Explore UI, understanding the difference between pivots and regular columns, and troubleshooting slow reports - involves a learning curve. As we got tired of constantly exporting CSVs or fighting with complex BI tools, we built Graphed to simplify this entire process. You can connect your data sources directly and bypass the manual setup by just asking in plain English something like, "Show me a pivot table of total sales by product category and region for last quarter," and get a real-time answer without clicking through menus and settings.