Data Studio
1.8K members online now
1.8K 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?