I need help with a calculated field. The data looks like below:
where content to week are rows and metric week1 and metric week2 are values.
I to to create a calculated metric using these 2 values as below -
Content Metric Region Station Week Metric
C123 M r 123 st12 10 (1+2)/(10+20)
Hello @misrsona , welcome to the Quicksight community!
You could try the calculations below if you Metric field isn’t too large:
Calc1 = Ifelse({Metric} = 'M1', sum(MetricWeek1) + sum(MetricWeek2), 0)
Calc2 =Ifelse({Metric} = 'M2', sum(MetricWeek1) + sum(MetricWeek2), 0)
Calc3 = Calc2/Calc1
My idea here is that you need to break out the MetricWeek1 and 2 columns but also have them be aware of Metric field. You may be able to also do this with a sumOver function.
Thanks for the input! I tried this and i think it might work. Only issue I am facing is the value after division is not correct. for eg, 473/440154 = 0.001074 but the calculated field is giving rounded value 0.001000.
Update -
I followed this post for the resolution - Availability of 20 decimal places but rounds to 4 decimal place - Calculation going wrong