How to create a percentage of column pivot table in Looker

Creating percentage of column and percentage of row pivot tables in Looker Studio is not as easy as in Excel or Google Sheets, because Looker Studio is missing this feature. My colleagues challenged me on it, and I ended up blaming Looker Studio for not being able to do it. Luckily, after many hours of wandering around the Google forums and doing more research, I finally found a complete way to do it. In this article, I’ll show you how to create a percentage of row or column pivot table in Looker Studio and explain how it works.

Types of percentage pivot tables

Suppose we have data with the following 4 columns.

In Looker Studio, you can create a pivot table based on the percentage of total without blending any data. This shows the distribution across both rows and columns, and it works nicely as a kind of heatmap.

However, Looker Studio doesn’t have the two percentage pivot types we need built in. The first type is percentage of column, where each column adds up to 100%. This one requires blending data.

The second type is percentage of row, where each row adds up to 100%. This one also requires blending data.

Once you have a clear picture of the result you want, move on to the next part: blending the data.

Blend data

To create a percentage of column or percentage of row pivot table in Looker Studio, you need to create the matching blends. Go to Resource, select Manage blends, then click the Add a blend button.

Blend for a percentage of one dimension

We’ll blend this data with itself, using Inner as the Join operator, and configure it as follows:

• Table 1: add the dimensions and metrics you need for the pivot table.
• Table 2: add only the one dimension whose total you want to be 100%. This is also the dimension used in the Join conditions. For the metrics, just give them different names. For example, I like to add “_denom” to the end of the metric name so it’s clear that it’s the denominator.

For example, if I want a percentage of column pivot table where the columns are product, no matter what the rows are, the blend configuration looks like this.

You’ll get a blended table like this. The first four columns are the same as the original data, and the only new column is orders_denom, which may look a bit confusing.

Let me explain the orders_denom column so you can see how it works.

• Looking at the results, the orders_denom column works like the query SUM(orders) OVER (PARTITION BY product) in SQL. The total number of orders for MacBook is 1601, so orders_denom shows 1601 on every row where the product is MacBook.
• Looker Studio aggregates each table before blending them together. Looking at the image above, we used SUM aggregation for the metrics.

Blend for a percentage of multiple dimensions

A percentage pivot of multiple dimensions gives the same results as the final pivot tables for each type (percentage of column or percentage of row) that I illustrated with Google Sheets above.

The method is the same as above. The only difference is that in Table 2, you add all the dimensions whose combined total should be 100% when stacked in columns or rows. These dimensions are also the Join conditions.

For example, if I want a pivot table with seller as the column, broken down by product, no matter what the rows are, the blend configuration looks like this.

Create a percentage pivot table in Looker Studio

For a percentage of total pivot table, use the original data (no blending needed) and set the metric’s Comparison calculation to Percent of total. It’s pretty easy, I’m just mentioning it for anyone who doesn’t know yet.

For percentage of column or percentage of row pivot tables, see the configuration details below.

Pivot table with percentage of column

To create a percentage of column pivot table in Looker Studio, configure the pivot table as follows.

• Data source: the blended data.
• Row: any dimension works.
• Column: only add the dimensions you used as join conditions in the blend.
• Totals: check Show grand total of Rows.
• Metric: create a metric with the formula SUM(orders) / MAX(orders_denom). Make sure to set Data Type to Percent and Display Format to Percent (2).

As a result, we now have three percentage of column pivot tables in Looker Studio, as shown on the left side of the image. Each column adds up to 100%.

Pivot table with percentage of row

A percentage of row pivot table in Looker Studio is configured the same way as a percentage of column one, except for these 3 settings.

• Row: only add the dimensions you used as join conditions in the blend.
• Column: any dimension works.
• Totals: check Show grand total of Columns.

As a result, we have 3 more percentage of row pivot tables, shown on the left side of the image. Each row adds up to 100%.

Conclusion

There is one limitation you should know about: pivot tables that use a blended data source can’t send cross-filters to charts that use a non-blended data source. Luckily, they can still receive cross-filters from other charts.

Doing this in Google Sheets or Excel for Mac is easy, but Looker Studio takes a lot more work, doesn’t it? I’m sure you’ll be happy once you have a pivot table where each row or column adds up to 100% in Looker Studio. Leave a comment below and let me know how it goes.