Create a measure with ifelse and the sum of another calculated measure

Question & Answer
,
1

Hello,

I am working on adding a new calculated measure under a certain condition.

  • I have a calculated field that classifies the device types, i.e: Mobile (M), Laptop (L), Tablet(T);
  • I have a calculated field that gives you the estimated CAC: (sum({metric1})-(sum({metric2})*0.70))/(sum({metric3})*0.55)

I want to create a new dimension as:
ifelse({clean_dev} = 'M', ({metric1}-({metric2}*0.50))/({metric3}*0.46), ({metric1}-({metric2}*0.70))/({metric3}*0.55))
However, it does not work, the value is 0:

Any ideas on how to fix it?

Thanks in advance!

2

@proma90 - Thank you for posting your query. Will you be able to share a sample snapshot of your dataset with the associated columns e.g. clean_dev, metric1, metric2, metric3 etc., so that we can try to replicate the issue at our end. Thank you!

1 Like
3

Hi @sagmukhe,

Thanks for reaching out.

Find below the screenshot of a sample data:

And the link to the google sheets: https://docs.google.com/spreadsheets/d/1ydGoSrpZYltea4eiqSv4Ob0lYmXIgPWMIpBfDjjCOno/edit?usp=sharing

thanks!

4

@proma90 - I have tried to take your sample data and recreated the data-table at my end and I am probably getting the expected values. Sharing the same with you.

image
image1093×575 28 KB

clean_dev is dimension whereas the remaining elements are measures. The Calculated field test is having the following logic. Please note that I haven’t used any aggregated function e.g. SUM within the if-else construct. Hope this helps!

ifelse({clean_dev} = ‘M’, ({metric_1}-({metric_2}*0.50))/({metric_3}*0.46), ({metric_1}-({metric_2}*0.70))/({metric_3}*0.55))

5

Hi @sagmukhe,
unfortunately, it does not work for me.
For more context, the clean_dev is a calculated field within the dataset.

6

@proma90 - This is weird. I hope the datatypes and aggregations are similar to what I have shared. Can you please try to build the calculated field step by step and see where it is failing. Perhaps at the first onset only have the calculation as metric_2 * 0.5, then next iteration try to do (metric_1 - (metric_2 * 0.5)). Just to see at which point it is breaking. Since I am getting the correct value or unable to replicate your problem, unfortunately not sure how can I help you over here.

7

Hi again @sagmukhe,

indeed, it fails when adding the division part.

When the calculation is as below I can see the values:
ifelse({clean_dev} = ‘M’, ({met1}-({met2}*0.50)), ({met1}-({met2}*0.70)))

But I can see that I do have values for metric 3…Any idea?

Thanks

8

@proma90 - Although, it should not behave this way ideally, but its good to know that, you have identified the breaking point. Can you please create another calculated field with something like :
ifelse({clean_dev} = ‘M’, ({met3} * 0.46), ({met3} * 0.55))

See if that works properly. If that works properly, then try to create a third calculated field by dividing the first calculated field (having the numerator portion) by the second calculated field (the denominator portion). I am feeling very unusual and uneasy in depicting these steps as it doesn’t seem logical. Unfortunately, I am just trying to provide some suggestions to make it work for you. As I mentioned, I didn’t find any difficult in getting the right numbers in the first pass itself. Let me know your findings (hope luck clicks this time!!)

9

A bit not logical, but that works, thanks!