Quicksight Pivot Table doesn't work on calculating differnce after collapse

[Yutong_Lin]

I want to use collapse in pivot table to show top reason only and show sub-reason only requested by users.
DoD diff in bps is calculated by difference of gv_count% comparing today with yesterday and times 1000. However, it is calculated based on difference of sub-reason. After collapse, I want it to show difference of top-reason dynamically. Is there any way to do that? Thanks

[Kellie_Burton]

@Yutong_Lin -
I believe you can utilize a approach as shown in the solution to the question in this thread.

How to divide two aggregated field value pivot table - quicksight during expand or collapse?

[image] |Column 1 | Column 2 | Column 3 | Column 4| |— | — | — | —| |2015 | 5 | 3 | 1.66| |2016 | 6 | 4 | 1.5| |CURRENT OUTPUT | 11 | 7 | 1.66 + 1.5 = 3.16| |DESIED OUTPUT | 11 | 7 | 11/7 = 1.57| | | | | | | | | | | I have a table where calculated field is showing value when collapse the pivot table. it aggregates table value instead of dividing by sum of colum2 / sum of colum3. I have attached an example for clarification. Can anyone please guide me further for this problem? Than…

Hope that helps!