How to calculate percentage of row using Google Data Studio Parameter

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_rand
GROUP BY
1) yy
ON
xx.region = yy.region
LEFT JOIN (
SELECT
Country,
SUM(Total_Revenue) AS Total_Revenue_Country
FROM
Sales_Summary_rand
GROUP BY
1) vv
ON
xx.Country = vv.Country

and Voila the final results, notice you can use any number of dimensions