I built a QuickSight pivot table with several levels of details, e.g. Region, Country, Customer and Store. I have Sales Revenue for a specific week, and Sales Revenue for the previous week (not displayed).
I would like to create a calculated field (in green) that replicates the results of the standard Subtotal field.
- If only the Region field is displayed, calculated field should display the sum partitioned by Region,
- If only the Region and Country fields are displayed, calculated field should display the sum partitioned by Region and Country,
Basically, I would need a sumOver with partitions that change dynamically depending on the expanded field(s).
Is there a method for this?
For context: this calculated field will be used eventually to calculate the contribution of the displayed field to change of Sales Revenue.