# Calculating the difference between two rows of pivot table data



## NorthupL

I have budget data that has 5 years of planned budgets for each program line in my portfolio that I want to compare with a new 5 year budget plan.  Is there a way to get a pivot table or PowerPivot to calculate the difference between the two rows of data so I can see how much the new budget changed from the previous budget? (instead of calculating a subtotal, calculate a difference)


----------



## tusharm

Not sure I understand what difference you want but try:

right click the data field column, select 'Value Field Settings...'  In the resulting dialog box, select the 'Show Values As' tab then 'Difference From' and Base Item: previous.


NorthupL said:


> I have budget data that has 5 years of planned budgets for each program line in my portfolio that I want to compare with a new 5 year budget plan.  Is there a way to get a pivot table or PowerPivot to calculate the difference between the two rows of data so I can see how much the new budget changed from the previous budget? (instead of calculating a subtotal, calculate a difference)


----------



## powerpivotpro

Can we see what your source data looks like?  Is it in one source table or two for instance?

PowerPivot measures don't operate on the data in the pivot itself, they operate on the source tables, and then just get displayed in the pivot.  So the "shape" of the source tables matters quite a bit.


----------



## NorthupL

powerpivotpro said:


> Can we see what your source data looks like?  Is it in one source table or two for instance?
> 
> PowerPivot measures don't operate on the data in the pivot itself, they operate on the source tables, and then just get displayed in the pivot.  So the "shape" of the source tables matters quite a bit.



Data is in two tables
as below.  Need to easily calculate the difference between Old Position and New Plan for each fiscal year without having to just insert a new row and subtract the two Positions.


PositionCOMMODITY IDPROGRAM IDPROGRAM NAMEITEMAPPNBABA BLININCREMENTFY11FY12FY13FY14FY15FY16FY17FY18FY19Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)RDTEBA4BA41195300000000Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA5 CA5 CONTAMINATION AVOIDANCE (SDD)RDTEBA5BA514902120290146818871111288603811100Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEVRDTEBA7BA71000000000Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSP MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)PROCPGP20001125536624150803469895081958899010900

<tbody>

</tbody>

PositionCOMMODITY IDPROGRAM IDPROGRAM NAMEITEMAPPNBABA BLININCREMENTFY11FY12FY13FY14FY15FY16FY17FY18FY19New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)RDTEBA4BA41195300000000New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA5 CA5 CONTAMINATION AVOIDANCE (SDD)RDTEBA5BA51490212029014681200000000New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEVRDTEBA7BA710000300050005000500011252New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSP MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)PROCPGP20001125536624150803499881258982721050001203260

<tbody>

</tbody>


----------



## NorthupL

What I was trying to do was instead of adding the two rows for a subtotal, get the difference between the two rows Orig Plan and New Plan as in the Pivot below.


PROGRAM NAMEBAPositionSum of FY11Sum of FY12Sum of FY13Sum of FY14Sum of FY15Sum of FY16Sum of FY17Sum of FY18CBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA4New Plan19530000000Orig Plan19530000000BA4 Total39060000000BA5New Plan49021202901468120000000Orig Plan490212029014681887111128860381110BA5 Total9804240580293621087111128860381110BA7New Plan00003000500050005000Orig Plan00000000BA7 Total00003000500050005000PNew Plan12553662415080349988125898272105000120326Orig Plan12553662415080346989508195889901090P Total25106132483016069696176339194161195109120326CBRN DISMOUNTED RECONNAISSANCE SYSTEMS Total127054538285952280567190467207764208220125326

<tbody>

</tbody>


----------



## tusharm

Consider using a SQL query (via Excel's MS Query tool).

If the 2 tables are in sheets named Orig and New, the below lists the difference for FY15.


		Code:
__


SELECT `New$`.APPN, `New$`.BA, `New$`.FY15-`Orig$`.FY15 AS 'Diff'
FROM `C:\Temp\Book1.xls`.`New$` `New$`, `C:\Temp\Book1.xls`.`Orig$` `Orig$`
WHERE `New$`.APPN = `Orig$`.APPN AND `New$`.BA = `Orig$`.BA


For an intro to MS Query see
Building and using a relational database in Excel (with a little help from MS Query)
RDBMS in Excel



NorthupL said:


> Data is in two tables
> as below.  Need to easily calculate the difference between Old Position and New Plan for each fiscal year without having to just insert a new row and subtract the two Positions.
> 
> 
> PositionCOMMODITY IDPROGRAM IDPROGRAM NAMEITEMAPPNBABA BLININCREMENTFY11FY12FY13FY14FY15FY16FY17FY18FY19Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)RDTEBA4BA41195300000000Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA5 CA5 CONTAMINATION AVOIDANCE (SDD)RDTEBA5BA514902120290146818871111288603811100Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEVRDTEBA7BA71000000000Orig PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSP MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)PROCPGP20001125536624150803469895081958899010900
> 
> <tbody>
> 
> </tbody>
> 
> PositionCOMMODITY IDPROGRAM IDPROGRAM NAMEITEMAPPNBABA BLININCREMENTFY11FY12FY13FY14FY15FY16FY17FY18FY19New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)RDTEBA4BA41195300000000New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA5 CA5 CONTAMINATION AVOIDANCE (SDD)RDTEBA5BA51490212029014681200000000New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSBA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEVRDTEBA7BA710000300050005000500011252New PlanCACBRN DRSCBRN DISMOUNTED RECONNAISSANCE SYSTEMSP MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)PROCPGP20001125536624150803499881258982721050001203260
> 
> <tbody>
> 
> </tbody>


----------



## JavierGuillen

You can do this by:

1) Loading each table into the PowerPivot window
2) Rename the Orig Plan table from 'Table1' to 'Orig Plan'
3) Rename the New Plan table from 'Table2' to 'New Plan'
4) On the New Plan table, right click FY11 and click insert column
5) Use the following DAX:

=CALCULATE( VALUES('New Plan'[FY11]), FILTER( 'New Plan' , 'New Plan'[BA BLIN] = 'Orig Plan'[BA BLIN]) )

6) Rename the calculated column to 'Orig FY11'
7) Do the sme for each FY value

You will now have all values side by side and can create a simple measure (or calculated column) that substracts the 'Orig FY11' from the new 'FY11' value.

Javier Guillen


----------



## MD610

A simple solution might be to just relate the tables on one of the unique columns and then use the RELATED funtion to pull the values from table 2 into table 1.  Then you will have all your fields in table 1 and calculating the difference will be as simple as subtracting the 2 fields in a calculated column or measure.


----------



## MD610

sorry, repeat post


----------



## NorthupL

Thanks for all the responses.  I ended up creating an additional column for each year that uses show value as diff and it works.  Gives 5 additional columns instead of one row.  MS Excel team should let you turn subtotal into a difference.


----------



## NorthupL

I have budget data that has 5 years of planned budgets for each program line in my portfolio that I want to compare with a new 5 year budget plan.  Is there a way to get a pivot table or PowerPivot to calculate the difference between the two rows of data so I can see how much the new budget changed from the previous budget? (instead of calculating a subtotal, calculate a difference)


----------



## Meckstss

tusharm said:


> Not sure I understand what difference you want but try:
> 
> right click the data field column, select 'Value Field Settings...' In the resulting dialog box, select the 'Show Values As' tab then 'Difference From' and Base Item: previous.



Awesome tip, thanks for this


----------



## tusharm

You are welcome. 


Meckstss said:


> Awesome tip, thanks for this


----------

