Pivot Table Subtotals/Variance Analysis

N

NCW

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!
 
C

Conrad Carlberg

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top