Adding a column to a pivot table that deducts one columns values from another

[Iain Garey]

Hi there

I have a pivot table that shows my actual values against what was budgeted, see screen shot attached.

Instead of the Grand Total column I would like a column that shows the variance between the Actual and Budget i.e. for 331020 Public Donations I would like to take the Budget value of £166 away from the Actual value of £137 to show an adverse variance of -£29.

Many thanks

Iain

Pivot.JPG

[xlnitwit]

Hi,
You need a calculated item (not a calculated field) for that but it looks to me as though you have grouped one of the fields? If so, you cannot use a calculated item. What you could do is create lookup tables for the groupings, and add new columns to the source data rather than grouping in the pivot field.

[Iain Garey]

Hi

I think I have now ungrouped the Actual and Budget columns and now have two separate columns of info. Is it now possible to enter a calculation field?

Many thanks

IainPivot2.JPG

[xlnitwit]

Now you want a calculated field (not item) just using the formula
='Actual value'-'Budget value'

[Iain Garey]

Excellent, was trying to do a calculated item at the end there, all working now.

Thanks for your help

[xlnitwit]

You're welcome.