Pivot Table Subtotals/Variance Analysis

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!

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>