Ist it possible to combine dimensions and metrics in calculated fields?

Question

We have the variables:

• "Unique User"
• "Version" (Plus, Light in a ratio 79:21 from all Unique User)
• "total Events"
• "Eventkatagories".

And following scenario: We can't get the exact data how many users are plus or light users. But we know how many events are triggered by version (plus/light).

Now we want to know how the relative frequency of events triggered grouped by Version and event category. So in a pivot table there is the row dimension = Version and the column Dimension = event category. So the measurement should be the relative frequency.

So the simple custom calculated field should be "total events / users"... But remember we can't get the absolute value of Users by Version, we just know the ratio (80-20).

So I build another calculated field called UsersbyVersion with following statement:

CASE
  WHEN (Version = "light") THEN SUM(User) * 0.21
  WHEN (Version = "Plus") THEN SUM(User) * 0.79
END

But this formula gives following error:

Invalid formula - Invalid input expression. - Failed to parse CASE statement

If I use absolute numbers for the statement it works. Example:

CASE
  WHEN (Version = "Normal") THEN 5000
  WHEN (Version = "Plus") THEN 25000
END

But we need the statement "User * ration" ... the ratio won't change a lot but the user value in relation to the date we want to set on the Data Studio Report.

So I guess the problem is that the statement won't work with a combination of metrics and dimensions. I already tried putting the "User * 0.79" and "User * 0.21" in custom metrics but this won't work aswell.

Is there a way to combine dimensions and metrics in a calculated field as an measurement?

Thx for your help

Answer 1

Create 2 metrics -

1. users * 0.2 (lets call this UsersP2)
2. users * 0.8 (lets call this UsersP8)

Now this should work

CASE
  WHEN (Version = "light") THEN UserP2
  WHEN (Version = "Plus") THEN UserP8
END

Dataset

Result