Table of Contents
Creating the percentage of row and percentage of column pivot tables in Looker Studio is not as easy as in Excel or Google Sheets because Looker Studio is missing this feature. My colleagues also challenged me, and I criticized Looker Studio for being unable to do it. Fortunately, after many hours of wandering on Google forums and researching, I finally had a complete way to do it. I will guide you in creating a pivot table with total percentage of column or row is 100% in Looker and explain its principles.
Percentage pivot table types
Suppose we have data consisting of 4 columns as follows.
In Looker Studio, you can create a percentage pivot table based on a percentage of the total without blending the data. This is to see the distribution on both dimensions: rows and columns. This style is helpful as a form of heatmap.
But Looker Studio doesn’t yet have the two percentage pivot table types we need. The first pivot table type is percentage of column; the sum of each column will be 100%. We need to blend data.
The second pivot table type is percentage of row; the sum of each row will be 100%. It is also necessary to blend the data.
Once you have visualized the result you want, go to the next part, which is Blending the data.
Blend data
You must create the corresponding blends to create a percentage of column or row pivot table in Looker Studio. Go to Resources, select Manage Blends, and click the Add a Blend button.
Blend for the percentage of a dimension
We will blend this data back into itself, using the Join operator Inner and configuring it as follows:
- Table 1: get the dimensions and metrics you need to use for the pivot table.
- Table 2: take only one dimension that you want its sum to be 100%; this is also the dimension used in Join conditions. For metrics, you need to rename them differently. For example, I like to rename metrics with “_denom” after them to understand the denominator.
For example, I want to create a pivot table with the percentage of column as the product column; regardless of the row, the blend configuration will be like this.
We will get a blend table like this: the first four columns are still the same as the original data, only a new column is orders_denom, which may confuse you.
Let me explain the orders_denom column so you can understand its working principle.
- Looking at the results, the orders_denom column looks like a query
SUM(orders) OVER (PARTITION BY product)in SQL. The total number of orders for MacBook is 1601, so the orders_denom column will display 1601 for rows whose product is MacBook. - Looker Studio will perform aggregation for each table before they are blended. We have performed SUM aggregation for the metrics in the image above.
Blend for the percentage of multiple dimensions
The pivot percentage of multiple dimensions will provide the same results as the final pivot tables of each pivot type (percentage of row or percentage of column) that I illustrated with Google Sheets above.
The method is still the same as above. The only difference is that in Table 2, you will add dimensions that you want their sum to be 100% when stacked in columns or rows; these dimensions are also Join conditions.
For example, I want to create a pivot table with a column for the seller and a breakdown down to the product; regardless of the row, the blend configuration will look like this.
Create a percentage pivot table in Looker Studio
We use the original data (no need to blend) and configure the metric in the comparison calculation as the percentage of total for the percentage of the total pivot table. It’s pretty straightforward; I repeat it for those who don’t know.
As for the percentage of column or percentage of row pivot table, see the configuration details below.
Pivot table with percentage of column
To create a percentage of column pivot table in Looker Studio, you configure the pivot table as follows.
- Data source: is the blended data table.
- Row: any dimension is acceptable.
- Column: only add dimensions you used as join conditions for the blend.
- Totals: check Show grand total of Rows.
- Metric: create a metric of
SUM(orders) / MAX(orders_denom). Selecting Data Type is Percent, and Display Format is Percent (2).
As a result, we have three percentage of column pivot tables in Looker Studio, as shown on the left side of the image. The sum of each column is 100%.
Pivot table with percentage of row
The configuration of the percentage of row pivot table in Looker Studio differs from percentage of column only in these 3 sections.
- Row: only adds the dimensions you used as the blend’s join condition.
- Column: any dimension is okay.
- Totals: check Show grand total of Columns.
As a result, we have 3 more pivot tables with percentrage of row on the left side of the image. The sum of each row is 100%.
Conclusion
There is a shortcoming that I want to let you know, which is that pivot tables whose Data source is blended cannot push cross-filter to charts whose data source is not blended, but luckily, they still receive cross-filter from the charts. another pushed over.
Working on Google Sheets and Excel for Mac is easy, but Looker Studio requires much effort. I believe you will be happy to create a pivot table with a total percentage of columns or columns of 100% in Looker Studio. Please leave a comment below the article to interact with me.
