Pivot table % totals issue

Question & Answer
, ,
1

Hi everyone,

We’re running into a recurring issue with user expectations around percentage totals in pivot tables, and I’m wondering if anyone has tips or workarounds to improve clarity.

We’re using a pivot table with multiple levels, subtotals, and totals. It includes:

  • YoY value difference
  • YoY % difference

QuickSight calculates totals by aggregating from the lowest-level data, which we understand – for % difference, it typically averages the underlying % values.

Users repeatedly report that the % total looks “wrong” because it doesn’t match what they get by doing a manual % = total diff / total base calculation.
They expect the % to be calculated directly from the displayed total values, not as an average of the rows.

We know QuickSight is technically doing the right thing (aggregating by column, averaging the % diff from each row), but from a user perspective, it’s confusing – especially when the total value sums correctly, but the % doesn’t “match” and when we have multiple levels as shown on second picture.
Appreciate any advice – even just how to better communicate this behaviour to users!
Thanks in advance.

image
image2902×686 96.5 KB

image
image3178×552 124 KB

2

Hi @JRoz

I think you are talking about something like this (Variance % is your case and what you want is Variance % Custom)

image
image948×503 43.2 KB

By computing the Variance % as follows you make it a Custom calculation and it will always work accordingly even at the Subtotal and Total level.

image
image478×186 3.49 KB

Regards,
Giri

3

Hi @Giridhar.Prabhu,
this unfortunately does not work in my case. I think maybe because I have to calculate LY weight using periodOverPeriodLastValue?

Then I calculate my difference like this:

{Weight diff} = sum({Weight})-{Weight LY} // this Weight LY already aggregated because thats how periodOverPeriodLastValue works as I understand

I cannot do then sum(Weight diff)/sum(Weight) as you showed, I do:

{Weight diff}/sum({Weight})

When I add it to pivot table the totals don’t show by default, I can select Totals → Average but this is exactly what I try to avoid.

Do you see how can I still resolve this problem?
Thanks in advance!