Pivot Table Calculations

[CherylC]

I frequently use pivot tables to analyze cost variances from one month to the
next. Is there a way to change the total column to show the difference
between the two months rather than the total of the two. I currently
copy/paste special/value the pivot table to a new sheet and then use formulas
to manipulate the data. Is there a better way using pivot table functions?

Thanks.

 

[Vinod]

Hi Cherly,

Place the cursor inside the table, then do the following
Go to Pivot table toolbar->Select Pivot Table Drop down list->Go to
Formulas->Select calculated Fields
Then Insert Calculated Field Window appears
At filed Name: Difference (write the field name you want, here i taken
difference)
At filed Formula: (Do like this)= double click on present month filed from
fields list -(minus sign) double click on pervious month
Click on Add button
select "Difference" from fields list
Click on Insert filed

This Difference field will be added in the pivot table and shows differences
between present month value and previous month value (which you selected at
formula)

Regards,
Vinod

 

[Roger Govier]

Hi Cheryl

Right click on PT>Table Options>uncheck Grand Total by Row
That will get rid of your Total column.

Right click on PT>PTWizard>Layout>grab your data field and add to the data
area a second time>
double click field label>choose Sum>Show Data as>Difference From>
Base field choose your Data filled>Base Item>Previous>OK>OK>Finish

On the PT, if the difference is showing on a separate line, as opposed to a
column, then drag the data icon across to Total

You will now have columns for the 1st Month, 1st Month Difference, 2nd
Month, 2nd Month Difference.
The column for 1st Month difference will always be blank, as there cannot be
a difference from itself, so you can Hide that column if required.