How to add allocation and available qty to pivot?

[panamajack]

Hello,

I have a pivot table that breaks down how much inventory has been consumed at the SKU, Region and Program levels. The inventory has also been allocated to the SKU, Region and Program levels, but I havent't firgured out a good way to reflect the allocation quantities in the pivot. Additionally, I would like to be able to calculate the difference between allocation and consumed inventory.

Here are the allocation quantities:
SKU Region Program Allocated Qty
5129B Americas 1PP 461
5129B Europe 1PP 257
5129B Asia 1PP 32
5129B Americas 3PP 661
5129B Europe 3PP 179
5129B Asia 3PP 210
5129B Americas Internal 1344
5129B Europe Internal 0
5129B Asia Internal 0
5129C Americas Beta 1058
5129C Europe Beta 300
5129C Asia Beta 90

Any help would be appreciated greatly!

Thanks,

PJ

[NBVC]

I am not sure what you mean by allocation quantities.. can you elaborate and perhaps with examples?

[panamajack]

NBVC, I added the allocation quantities I referered to to my first post. My goal is to have the pivot indicate how much inventory has been consumed, how much was allocated and the difference between thte two.

Here is an example:
461 units of SKU 5129B have been allocated to the 1PP program in the Americas Region. According to the Pivot 381 units have been consumed, The difference is 80 units.

I would like all of this data to be included in the pivot, but can't figure out how to make it work.

[NBVC]

If that is a separate table, then I don't think you can converge them into a pivot table, unless you somehow add a column to your main table, such that the Allocation column would come out to the numbers you expect.

Another way, is to add a formula outside the pivot table.... but first you should change the layout of the Pivot table to Table Form, so that each header is an separate column. so then your pivot table would occupy A5:E46, then in F6 add formula:

=SUMPRODUCT(--(Sheet1!$H$2:$H$13=LOOKUP(REPT("Z",255),$A$5:$A6)),--(Sheet1!$I$2:$I$13=TRIM(SUBSTITUTE(LOOKUP(REPT("z",255),$B$5:$B6),"Total",""))),--(Sheet1!$J$2:$J$13=TRIM(SUBSTITUTE(LOOKUP(REPT("z",255),$C$5:$C6),"Total",""))),Sheet1!$K$2:$K$13)

copied down.

where your Allocations table is in Sheet1!H1:K13.

Then you simply subtract...the two in G6 with =F6-G6 copied down.

See attached to see if it fits your needs.

Note: You can add to the formulas, to leave certain cells blank (for instance the total lines, you might not care to see a formula in F and G)...