michaelblease
New Member
- Joined
- Jan 24, 2010
- Messages
- 1
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.
<tbody>
</tbody>
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.
| Item | Avg Pos | Sum of Job Lot | Inventory | Suprlus/Shortage |
| A | 0 | 12 | 8 | -4 |
<tbody>
</tbody>
