Search code examples
maxaverageminlooker-studioklipfolio

How do I get "Difference from min" in Google Data Studio?

I'm trying to convert my Klipfolio expression into a Google Data Studio chart expression for CTR Lift. CTR Lift is the percentage of difference each grouped result has from the minimum result. The expression is as follows:

&Column: CTR / REPEAT(MIN(&Column: CTR), COUNT(&Column: CTR)) - 1

I'm trying to recreate this as a chart field (it cannot be recreated as a data source field), but there's no "Difference from min" option:

CTR Lift AVG(CTR) Difference from max + Relative to corresponding data

Only percent/difference/percent difference from max is available, no min option:

Only percent/difference from max is available

How can I made Google Data Studio compare to min, instead of comparing to max?

I've tried things like 100 - AVG(CTR) or AVG(CTR) * -1, but they don't seem to match my Klipfolio data.

Solution

  • It turns out that AVG(CTR) * -1 is the correct syntax to compare to minimum, but I had to select, "Percent difference from max / Relative to corresponding data". The data set that I was comparing to to validate was inaccurate.