Pivot table with more than one grand total

[Costas]

Hi Chandoo,

I've set up a pivot table to compare sales by salesperson actual vs budget (see attached photo).
I have two issues:

• The grand total adds actual and budget values into something that is meaningless to me.
• The variance % does not show in the grand total but I guess if we fix the first point the second would solve itself.

Thanks
Costas

 

[pecoflyer]

Please post a sample sheet with some data and expected results (pics are nice but allas useless)

 

[Costas]

Hi pecoflyer,

Please see attached excel file. The expected results tab shows what I'd like to see in the pivot table tab.

The main difference is in the grand totals showing totals for actual, budget and variance.

Thanks
Costas

 

[GraH - Guido]

perhaps, like this...

 

[Costas]

Hi GraH - Guido,

This is what I'm after in the grand totals but I lose the setup I had for each salesperson as per my expected results tab.

Thanks anyway
Costas

 

[bosco_yip]

Hi GraH - Guido,

This is what I'm after in the grand totals but I lose the setup I had for each salesperson as per my expected results tab.

Thanks anyway
Costas

Maybe,

1] Add 2 rows behind "Grand Total" for "Actual" and "Budget"

2] In B31, copied down to B32, then copied/paste to D31:D32 and F31:F32

=SUMPRODUCT(($A$7:$A$28=$A31)*B$7:B$28)

3 In C31, copied/paste to E31 and G31

=(B31-B32)/B32

Regards
Bosco