Can a pivot table calculate the difference between two fields

T

tnlclifton

My pivot table is summarized by group, then by Actual Headcount and Budget
Headcount. So for example, HR is the first category broken down into 10
heads of budget and 9 heads of actual. The Total line sums the Budget and
Actual total to show 19. I would like the Total line to show the difference
between Budget and Actuals. So instead of the sum of 19, I would like the
pivot table to calculate the delta (Delta would equal 1: Budget of 10 -
Actual of 9). I would like it to do this for each group (HR, Sales, etc.)
Any thoughts?
 
R

Roger Govier

Hi

If Budget and Actual are Fields (columns in your source data), then from
the PT Toolbar dropdown, select Formulas>Calculated Field
Set Name to Delta and Formula to =Actual-Budget
You will now see an additional set of data for the difference.

If Budget and Actual are different items in the same Field (same column)
then from the PT dropdown choose Formulas>Calculated Item and proceed as
above
 

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

Similar Threads

Pivot Table 2007 display field only in Grand Total 0
Doughnut Chart linked with the slicer in the pivot table 0
Summing multiple departments 1
Pivot table and variance calculation 1
Pivot table to calculate variance showing budget and actual. 3
pivot table calc item 8
Pivot Table Set Up 2
"Dynamic" calculated fields in a pivot table 0

Top