CODEWITHSUNDEEP
looker-studio
kee
kee

Reputation: 11629

GCP Datastore: how to show ratio in Scorecard

I wanted to show a ratio in a scorecard in my dashboard in Google DataStudio but it is not working as I hoped.

I have this table named daily_store_transactions:

date datestamp,
store string,
key string,
value int64

Here key can have two values of "total" and "success". value has corresponding count. I wanted to show this ratio of success/total as a scorecard in my DataStudio dashboard.

First I defined a new field called "total" as the following:

CASE WHEN key = "total" THEN value  ELSE 0 END

Next I defined a new field called "success" as the following:

CASE WHEN key = "success" THEN value  ELSE 0 END

So far so good. Finally I created the last field named "successRatio" as the following:

success / total

When I changed the metric of the scorecard as this last field (successRatio). SUM was picked up as the aggregation function and the displayed value was exactly the same as success somehow. I have no idea how to fix this. Should I change my table schema? I tried to change the aggregation function but nothing worked out.

Upvotes: 2

Views: 211

Answers (1)

Bobbylank
Bobbylank

Reputation: 1946

I think what you're after is:

Total

sum(CASE WHEN key = "total" THEN value  ELSE 0 END)

Success

sum(CASE WHEN key = "success" THEN value  ELSE 0 END)

And then successRatio stays the same.

If not, pop so example data and expected outcome up

Upvotes: 3

Related Questions