Incorrect Total for level-aware calculation

Question & Answer
,
1

Hi all,
I’m using Table in Quicksight and I have below fields (more detail in attached snapshot)
Raw data: Group by: category, Value: revenue, cost
Calculation:
1/ rate: revenue/ cost
2/ % of total cost: cost/ total cost
3/ rate YoY: (rate current year - rate prior year)* 10000
4/ % of total cost YoY: (% of total cost current year - % of total cost prior year)x 10000
5/ calculation 1: % of total cost * rate YoY
6/ calculation 2: % of total cost YoY * (rate - total rate)
7/ calculation 3: calculation 1 + calculation 2

I need the total of each calculation 1,2,3 as highlighted in Green in the attached snapshot but Quicksight give me results in yellow. I simply need the total of all categories as if we do sum of all rows with Excel

Found similar question here in stackoverflow but no answer: amazon redshift - Quicksight calculated filed displayed total wrong - Stack Overflow

quicksight_question
quicksight_question1908×684 122 KB

Thank you

2

Hi zack, did you upload the excel sheet directly to Quicksight or via S3? If via S3, please check the manifest file, the The URIPrefixes should be empty. Otherwise Quicksight will upload multiple files under that prefix. Although you see the dataset value is correct, but the calculation will be wrong.

4

I figured out one way to get the correct total of calculation 1 is by hardcoded the cat to get the number in each line, then sum them up, wonder if there is a better/ dynamic way to do this

//{rate YoY} * percentOfTotal(sumIf({cost}, PYTD = 'Y'))

// cat 1
(
(sumIf({revenue}, YTD = 'Y' and {cat} = 'cat1')/sumIf({cost}, YTD = 'Y'  and {cat} = 'cat1'))
-
(sumIf({revenue}, PYTD = 'Y'  and {cat} = 'cat1')/sumIf({cost}, PYTD = 'Y'  and {cat} = 'cat1'))
)

* 10000

*
(
sumIf({cost}, PYTD = 'Y'  and {cat} = 'cat1')
/ 
sumIf({cost}, PYTD = 'Y')
)

+

// cat 2
(
(sumIf({revenue}, YTD = 'Y' and {cat} = 'cat2')/sumIf({cost}, YTD = 'Y'  and {cat} = 'cat2'))
-
(sumIf({revenue}, PYTD = 'Y'  and {cat} = 'cat2')/sumIf({cost}, PYTD = 'Y'  and {cat} = 'cat2'))
)

* 10000

*
(
sumIf({cost}, PYTD = 'Y'  and {cat} = 'cat2')
/ 
sumIf({cost}, PYTD = 'Y')
)

+

... similar for cat3 to cat 5
5

Hi Sophia,
The excel in snapshot is just to clarify the issue, my dataset is a table in redshift. Here is a more simplify example from another user: amazon redshift - Quicksight calculated filed displayed total wrong - Stack Overflow

6

Hi Zack, it seems you’re trying to create a sum of calculated field. Quicksight will not simply added all values of the calculated field, instead, it’ll follow the formula of calculated field to calculate the relation of sums in other columns.

Please refer to our Quicksight Team’s response in another thread:“QuickSight does not currently support aggregation type changes for totals. Your best approach is to create your calculation at the dataset level, at the proper granularity. Then in the analysis, you can add the new calculation to the visual as ‘sum’. This should give you the ability to have a summed total.”

1 Like