Calculated field to do division of rows

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

1 Like

Hey @misrsona , I’m glad that other post helped!