Pivot Table Calculated Field Error

[michaelblease]

I have a pivot table and one of the values I changed the value field setting to "AVERAGE" but the calculated field is calculating based off of sum. How can I get the calculated field to calculate off of the average?

The Surplus Shortage field is my calculated field where I am taking Inventory less Job Lot. As you can see the "SUM" is 12 but that is because we have this sku listed 3 times in the excel file that is feeding the pivot.

If I change the "SUM OF JOB LOT" to "AVERAGE OF JOB LOT" the figure will be 4 but my calculated field "Surplus/Shortage" will remain as -4 rather than changing to +4.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Avg Pos[/TD]
[TD]Sum of Job Lot[/TD]
[TD]Inventory[/TD]
[TD]Suprlus/Shortage[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]-4[/TD]
[/TR]
</tbody>[/TABLE]