Hi,
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.
