Pivot Table Percentage Change

[yeshua68]

Hello,

I have a pivot table that shows the usage of a material. It shows the target amount of boxes, the actual usage of boxes and the variance between the target and actual. The columns are in the values field.

I can't seem to find a way to get excel to use the values field to create a column for % change in usage. For instance, our target of boxes was 1000, our actual was 3000 with a variance of 2000 boxes. I'd like the pivot table to show that we used 200% of our actual target.


I can do it using a formula in my raw data and just pull it from there. But I'd rather the pivot table have a column using "show values as". But when I use the "% Difference" option - it only gives me the columns and rows as options and not the value fields.

Is there a way?

 

[BSLeBlanc]

In the PivotTable Tools ribbon, select Fields, Items, & Sets dropdown from the Calculations section then select Calculated Field....

Name your field (i.e. Variance %). For formula, enter your variance field name then type / then select your target field name (i.e. =Variance/Target). Click Add.