Create calculated field which works for multiple hierarchical columns


We are currently experiencing an issue with Quicksight and require your assistance. Specifically, we are having trouble displaying the correct values for our data set.

To provide some context, our data set contains four columns: SuperRegion, Station, Numerator, and Denominator. Our objective is to display the sum of the Numerator divided by the sum of the Denominator for both SuperRegion and Station in separate visuals.

To achieve this, we created a calculated field using the formula mentioned above (i.e sum(num)/sum(denom)). However, while the formula works correctly for the Station, it does not display the correct value for the SuperRegion. Instead of summing the Numerator and Denominator for the SuperRegion, it displays an average of the station-level values.

After researching a solution, we discovered a formula that works for the SuperRegion: sumOver({numer}, [SuperRegion], PRE_AGG)/sumOver({denom}, [SuperRegion], PRE_AGG). However, we would like to create a single formula that works for both Station and SuperRegion instead of creating separate formulas for each.

I have also attached a screenshot for your reference. The second column in the screenshot displays an incorrect value as it shows the average of station-level values, while the third column is calculating the correct value.

Can you add another partition of station? sumOver({numer}, [SuperRegion,Station], PRE_AGG)/sumOver({denom}, [SuperRegion,Station], PRE_AGG)

Hi @kachandn! It looks like the second column in the screenshot (utilization) is computing ratio(num/denom) at row level then you are doing an Average. Can you please share a screenshot of the formula for utilization column?
You may be using num/denom instead of sum(num)/sum(denom). If you use sum(num)/sum(denom) then the aggregation at the visual level will be defaulted to ‘custom’ instead of you being able to select average and will work for both SuperRegion and Station dimensions.

Corrected one

Hope this helps!

1 Like