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

Change the color of a bar chart based on a value using Google Data Studio Parameter

This one I took it from a Tableau Presentation, let’s say you want some element to have a particular color when they reach a value.

For example in this report, I want to make it clear which generators is producing more than 1000 MW.

Create a parameter MWREF

Data Studio formula engine you can not compare dimension to parameter, instead we create first a helper calculated field “diff”

then we create a second calculated field “color”

in the bar chart, I use “color” as as a breakdown Dimension

and here is the result

Create Dynamic goal line on a bar Chart using Google Data Studio Parameter

TL;DR : The report is here

trying to reproduce a visual I saw before, Probably in a tableau forum, it is quite simple but give a very nice visual clue, the idea is the user input a target and the color will change based if it is higher or lower than the Target

Probably you can do it using Parameter in Google Data Studio, but using BigQuery was much easier. ( solution using only GDS , courtesy of Nimantha )

I built this Query, which generate two values, Firstsection of the bar and the secondsection

SELECT
*,
CASE
WHEN MW < @target THEN MW
ELSE
@target
END
AS firstsection,
CASE
WHEN MW < @target THEN null
ELSE
MW -@target
END
AS secondsection
FROM
datastudio.table

define parameter “Target” , currently BigQuery parameter does not accept range, instead you have to type a number

then Create Stacked Combo Chart

Make sure first section and second section are bars and target is a line and make sure bars are stacked

when you change the parameter values, the bars value change

Dynamic Change of Dimension using Google Data Studio Parameter

At Last Google Data Studio added the option to let the user change the value of parameter, which will make some new scenarios possible, I will try to show some new cases where either it was extremely painful to do, or simply not possible.

In this report, I added some cases where I think it is useful, for this Blog, I will start with a very common scenario

The report Show the Daily Electricity produced in eastern state of Australia, just by Using a slicer, the level of details will change to Region or Technology, or individual Generators

Currently it does not work with parameter in the formula engine,when I tried I got this error ( Nimantha has a solution using Regex which does not require BigQuery, you can see his report here)

Update as 26 August 2020

Riccardo from the dev team suggested a simple workaround,

let’s just create a dummy dimension that take the values from the parameter

( I swear, I first tried this before, but it was not working, anyway calculated field in GDS are still a mystery for me)

now you can use it in your calculation

Alternatively you can used a Custom Query from BigQuery, luckily it is accelerated by BI Engine, so it is fast and use the free 1 GB memory provided by Data Studio

SELECT
*,
CASE
WHEN @detail="Region" THEN Region
WHEN @detail="FuelSourceDescriptor" THEN FuelSourceDescriptor
WHEN @detail="StationName" THEN StationName
WHEN @detail="Technology" THEN Technology
ELSE
NULL
END
AS Level_detail
FROM
datastudio.today_view_MT

then you added the values to the parameter, notice, you can’t have a list of values from a data source, you have to manually type the values.

now the column “Level_detail” will dynamically switch to column “Technology”, “Region” etc based on the selected value in the parameter Detail

now you add the Parameter “detail” to a filter control, notice Parameter are color code Pink, a nice visual clue !!

now you use your dynamic column in a visual

and here is the final results

Personally I think it is a game changer for Data Studio, specially when you combine it with the Power of BI Engine, interesting time ahead