TL;DR : Report here
let’s say you work with sales data, and you have being asked to do this analysis
1- Give me sales number by region and Item type
very easy using a pivot table
2- Give me percentage by item and Region
It is very easy using comparison calculation
and here is the report
We Have a problem the percentage is compared to the overall sales, usually we are interested in the ratio of item sold by region !!!
It is a very common request, see this question in the forum
3- Workaround using Blending
One approach is to use blending to calculate the total sales per region then divide by items, see tutorial here , it is fair to say, it is one of the most popular report I built, it seems it is a common pain point
4- I want to see the sales per region and by Country
The workaround using blending is hard coded to one dimension, let’s say region, but usually users want to drill down to multiple level,
I guess you can write a SQL Query to achieve this scenario, but you need to duplicate the data which is something I don’t particularly like !!!
5- Parameter and SQL, you can do anything
we do have parameter now, I am using the same technique as described here
basically, I write a SQL query to calculate the total sales per region and total sales per country, and using filter control , the user can switch to either dimension, as August 2020 BI Engine does not support Sum Over(), I am using self join
WITH xx AS ( SELECT region, Country, Item_Type, SUM(Total_Revenue) AS Total_Revenue FROM
Sales_Summary_randxx GROUP BY 1, 2, 3) SELECT xx.region, xx.Country, Item_Type, Total_Revenue, Total_Revenue_Region, Total_Revenue_Country, CASE WHEN @details="region" THEN xx.region ELSE xx.country END AS Level_details, CASE WHEN @details="region" THEN Total_Revenue/Total_Revenue_Region ELSE Total_Revenue/Total_Revenue_Country END AS Percent, FROM xx LEFT JOIN ( SELECT Region, SUM(Total_Revenue) AS Total_Revenue_Region FROM
Sales_Summary_randGROUP BY 1) yy ON xx.region = yy.region LEFT JOIN ( SELECT Country, SUM(Total_Revenue) AS Total_Revenue_Country FROM
Sales_Summary_randGROUP BY 1) vv ON xx.Country = vv.Country
and Voila the final results, notice you can use any number of dimensions
6 thoughts on “How to calculate percentage of row using Google Data Studio Parameter”
Thanks mim for the explanation, great workaround.
I have a question related to workaround with blending: how would you do it if you had to calculate percentage of region with data blending? I see the example with week in the percentage row explanation but can’t figure out how it would work with a different dimension
I’m having a similar issue – I need to calculate percentage of positive & negative responses to a range of survey questions
The title is misleading. This is the percentage of the column total. Not percentage of the Row total.
Can this be done for the row instead?