Fix Avg. % Calculation Error in Google Data Studio
Hi there, if you use Google Data Studio for reporting, there could be an overlooked error occuring when calculating average percentages over a period of time (e.g. ROI, P/L%, etc). In our case below, we found P/L over the month of March and wanted to find the P/L% which is
(Revenue – Cost) ÷ Cost between 3/1/19 and 3/31/19.
In other words, ROI for the month of March.
If you see below:
39,961 ÷ 117,016 ≠ 10.91
.png)
In the case you calculated P/L % this way, the scorecard defaults to “Sum” Aggregation which is why the custom field returned the value of 10.91, instead of a decimal that would denote a percentage. To rename or change aggregation, you would just click on ‘SUM’ next to the metric name, it changes to a pencil icon once you hover over it.
.png)
.png)
Even if you choose “Average” aggregation, the result (Under ‘False P/L %’) is still wrong. Shown below:
.png)
NOTE: This was a step to change aggregation settings. Despite being close, this was NOT the fix.
In order to fix the issue you need to edit the calculated field you’ve created. It might look like this if you are not getting the correct P/L % :
.png)
.png)
The key here is to use the SUM( ) function to correctly aggregate your data before having it run through the formula. If you use the SUM( ) function on P/L, you would also need to use the same function for Total Cost. Otherwise, you’ll get an error message saying “calculated fields can’t mix metrics and dimensions.”
Should you ever need any more functions you can find more here:
https://support.google.com/datastudio/table/6379764?hl=en
After correctly formatting your calculated field, your discrepancies should be fixed!
