Good Morning,
I am trying to build a pivot table that will calculate some very simple things but I cant seem to get it right. Currently, the pivot table looks as follows:
FC Region MIDWEST
Send Truck Stock (Y/N) Y
Active/Inactive (Multiple Items)
Sum of Onhand Qty Employee No
Part No Midwest AI100713 AI100748 AI100900 AI101869 AI101974 AI102052 AI102053 On Hand
0CZZA20007A 3 3 3 6
0CZZJB2014K 1 1 1 1 1 1 1 1 7
2B71165R 1 1 1 1 1 1 1 1 7
3034JA1009A 1 1 1 1 1 1 1 1 7
3550C-0042C 1 1 1 1 1 1 1 1 7
3550JJ1070B 1 1 1 1 1 1 1 1 7
3891ER2003A 1 1 1 1 1 1 1 1 7
4413EA1002B 1 1 1 1 3
4413EA1004D 1 1 1 1 1 1 1 1 7
I need to create a formula in the pivot table that will take the value in the column called "Midwest" and subtract it from the value associated with each part number for the appropriate employee no. This calculation can be called "Difference".
For columns with no value, they are considered to be zero (ex. Midwest (3) - AI100713 (0)). This calculation would need to be done for each employee no. Once that is done, I need a total column that will sum both the parts on hand for each employee and the "Difference". So the outcome would look like this
AI1009713 AI1009713 (Difference) AI100748 AI100748(Difference) AI102052 AI102052 (Difference) Onhand SUM
Row Labels Midwest
0CZZA20007A 3 0 3 (=3-0) 0 3 (=3-0) 3 0 (=3-3) 3 9
Currently, the pivot table will not allow me to create a calculation based off the Midwest column because it is a row label and not a column label or value.
Please help. It would be greatly appreciated.
Bookmarks