I am trying to get a pivot table to show a variance column for the
subtotals of 2 sets of grouped data. This is for summarising all
financial transactions by period (column) by funding source and cost
centre (rows), and then comparing this to the budget amount.

I have added a pivot table field using options to find the difference
between these 2 groups (transactions and budget). It does return the
correct calculation but also inserts a variance column for each
individual column and I can't figure out a way to hide or disable the
individual variance columns whilst leaving the subtotal variance column
showing.

As an alternative to the above I have also tried using the calculated
fields option to create subtotals of the 2 sets of data but without
success, and using calculated items inserts too many extra rows (for
example funding source of capital shows all cost centres not just the
cost centres that are capital funded).

Any advice appreciated!


--
NCW
------------------------------------------------------------------------
NCW's Profile: http://www.excelforum.com/member.php...o&userid=25993
View this thread: http://www.excelforum.com/showthread...hreadid=393568

Seems kind of simpleminded solution, but have you considered doing your
variance analysis outside the pivot table? BTW, the GETPIVOTDATA function
can be handy for doing that.

C^2
Conrad Carlberg

"NCW" wrote in message
...

I am trying to get a pivot table to show a variance column for the
subtotals of 2 sets of grouped data. This is for summarising all
financial transactions by period (column) by funding source and cost
centre (rows), and then comparing this to the budget amount.

I have added a pivot table field using options to find the difference
between these 2 groups (transactions and budget). It does return the
correct calculation but also inserts a variance column for each
individual column and I can't figure out a way to hide or disable the
individual variance columns whilst leaving the subtotal variance column
showing.

As an alternative to the above I have also tried using the calculated
fields option to create subtotals of the 2 sets of data but without
success, and using calculated items inserts too many extra rows (for
example funding source of capital shows all cost centres not just the
cost centres that are capital funded).

Any advice appreciated!


--
NCW
------------------------------------------------------------------------
NCW's Profile:
http://www.excelforum.com/member.php...o&userid=25993
View this thread: http://www.excelforum.com/showthread...hreadid=393568