How do I pull data from two Pivot Tables simultaneously and then perform a subtraction operation and get the result on any sheet?

Question

I have a Stock Transfer's report from which I have made a Pivot Table. The pivot table shows the grand total of all the Received Quantities.

I have a Delivery Report from which I have made a Pivot Table. The pivot table shows the grand total of all the Shipped Quantities.

Now, I want that I can get the Balance Quantities (i.e. Received Quantities - Shipped Quantities) a simple subtraction operation but the data has to be pulled from pivot tables.

Please guide me through the process. Many Thanks.

Answer 1

Hi Basil,

Based on your description, you have two report and want to get the balance quantities.

Here is the result.

Steps:

1.get a list with unique value of stock (Table3)

2.Create data model and choose "table 3"

3.Create relationship for Table 1 and Table 2 with Table 3

4.Select the Sum of shipped quan and Sum of received quan

5.Choose the power povit add for excel add in from developer

6.Add Measures to power povit

Hope it helps. Let me know if you have any problem.

Best Regards,

Snow Lu

Answer 2

If you just want one grand total minus the other, use two GETPIVOTDATA formulas. They should auto-generate by default when you select the cells in question, unless you turned that option off.