AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
2.5K members online now
2.5K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

CALCULATED FIELD: CASE

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am having some issues created a calculated field. I want to calculate the profit of each of my SKUs. Hence, I would like to know if it is possible to enter a formula after "THEN", but it seems not to be possible. Here is the formula I wanted to create:

 

CASE WHEN Product SKU = "000-01U" THEN "Product Revenue - (Quantity * 4.05)" WHEN Product SKU = "000-02U" THEN "Product Revenue - (Quantity * 4.05)" Product SKU = "000-03U" THEN "Product Revenue - (Quantity * 4.05)" END (and so on...

 

But it seems not to work. Can you help me here? Any workaround?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Nicolas C
March 2017

CALCULATED FIELD: CASE

Follower ✭ ✭ ✭
# 3
Follower ✭ ✭ ✭

Case functions and metrics don't play nicely together. Depending on how you want to display your results, here's a work around:

 

Create a Calculated Function Called 000-01U = Case When Product SKU = '000-01U' Then 1 Otherwise 0 End.  (Aggregation; Sum ie a Metric)

 

Create another Calculated Function Product Example 01U = 000-01U * (Product Revenue - (Quantity * 4.05))

 

Yes, we're back to binary.  Yes, you'll need to create quite a few depending on 000-xU.

 

Otherwise, I believe Case Metrics are coming so too are filtering by Dimensions. Could you also, not create a function; Product Revenue - (Quantity * 4.05) and add a Breakdown Dimension in your chart by Product SKU?

View solution in original post

CALCULATED FIELD: CASE

Participant ✭ ✭ ☆
# 2
Participant ✭ ✭ ☆

I'm curious as well, as I haven't had luck with case statements. I think that a case statement might only be available with dimensions (rather than the dimension-metric mixture you have here). Happy to be proven wrong, if anyone has more info. 

Marked as Best Answer.
Solution
Accepted by topic author Nicolas C
March 2017

CALCULATED FIELD: CASE

Follower ✭ ✭ ✭
# 3
Follower ✭ ✭ ✭

Case functions and metrics don't play nicely together. Depending on how you want to display your results, here's a work around:

 

Create a Calculated Function Called 000-01U = Case When Product SKU = '000-01U' Then 1 Otherwise 0 End.  (Aggregation; Sum ie a Metric)

 

Create another Calculated Function Product Example 01U = 000-01U * (Product Revenue - (Quantity * 4.05))

 

Yes, we're back to binary.  Yes, you'll need to create quite a few depending on 000-xU.

 

Otherwise, I believe Case Metrics are coming so too are filtering by Dimensions. Could you also, not create a function; Product Revenue - (Quantity * 4.05) and add a Breakdown Dimension in your chart by Product SKU?

CALCULATED FIELD: CASE

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Thank you Paul,

 

The second option would not work unfortunately as my costs are not the same for each SKU. (ie: 000-01U = 4.05€ ; SKU 000-02U = 5.07€ ; SKU 000-03U = 3.02€ ...).

 

However, your first workaround suggestion looks like an interesting option. I am not sure it would work in my case if I want to show up a breakdown dimension menu with gross profit for each SKU. But something close should work.

 

This way, it could work if I can make the CASE formula with SKU refer to one specific calculated function... Same way goes for all the other SKUs I guess?