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.

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]

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.

Position	COMMODITY ID	PROGRAM ID	PROGRAM NAME	ITEM	APPN	BA	BA BLIN	INCREMENT	FY11	FY12	FY13	FY14	FY15	FY16	FY17	FY18	FY19
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)	RDTE	BA4	BA4	1	1953	0	0	0	0	0	0	0	0
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA5 CA5 CONTAMINATION AVOIDANCE (SDD)	RDTE	BA5	BA5	1	49021	20290	14681	8871	11128	8603	8111	0	0
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEV	RDTE	BA7	BA7	1	0	0	0	0	0	0	0	0	0
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	P MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)	PROC	P	GP2000	1	12553	6624	15080	34698	95081	95889	90109	0	0

<tbody>
</tbody>

Position	COMMODITY ID	PROGRAM ID	PROGRAM NAME	ITEM	APPN	BA	BA BLIN	INCREMENT	FY11	FY12	FY13	FY14	FY15	FY16	FY17	FY18	FY19
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)	RDTE	BA4	BA4	1	1953	0	0	0	0	0	0	0	0
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA5 CA5 CONTAMINATION AVOIDANCE (SDD)	RDTE	BA5	BA5	1	49021	20290	14681	2000	0	0	0	0	0
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEV	RDTE	BA7	BA7	1	0	0	0	0	3000	5000	5000	5000	11252
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	P MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)	PROC	P	GP2000	1	12553	6624	15080	34998	81258	98272	105000	120326	0

<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 NAME	BA	Position	Sum of FY11	Sum of FY12	Sum of FY13	Sum of FY14	Sum of FY15	Sum of FY16	Sum of FY17	Sum of FY18
CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA4	New Plan	1953	0	0	0	0	0	0	0
		Orig Plan	1953	0	0	0	0	0	0	0
	BA4 Total		3906	0	0	0	0	0	0	0
	BA5	New Plan	49021	20290	14681	2000	0	0	0	0
		Orig Plan	49021	20290	14681	8871	11128	8603	8111	0
	BA5 Total		98042	40580	29362	10871	11128	8603	8111	0
	BA7	New Plan	0	0	0	0	3000	5000	5000	5000
		Orig Plan	0	0	0	0	0	0	0	0
	BA7 Total		0	0	0	0	3000	5000	5000	5000
	P	New Plan	12553	6624	15080	34998	81258	98272	105000	120326
		Orig Plan	12553	6624	15080	34698	95081	95889	90109	0
	P Total		25106	13248	30160	69696	176339	194161	195109	120326
CBRN DISMOUNTED RECONNAISSANCE SYSTEMS Total	127054	53828	59522	80567	190467	207764	208220	125326

<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.

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

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.

Position	COMMODITY ID	PROGRAM ID	PROGRAM NAME	ITEM	APPN	BA	BA BLIN	INCREMENT	FY11	FY12	FY13	FY14	FY15	FY16	FY17	FY18	FY19
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)	RDTE	BA4	BA4	1	1953	0	0	0	0	0	0	0	0
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA5 CA5 CONTAMINATION AVOIDANCE (SDD)	RDTE	BA5	BA5	1	49021	20290	14681	8871	11128	8603	8111	0	0
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEV	RDTE	BA7	BA7	1	0	0	0	0	0	0	0	0	0
Orig Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	P MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)	PROC	P	GP2000	1	12553	6624	15080	34698	95081	95889	90109	0	0

<tbody>
</tbody>

Position	COMMODITY ID	PROGRAM ID	PROGRAM NAME	ITEM	APPN	BA	BA BLIN	INCREMENT	FY11	FY12	FY13	FY14	FY15	FY16	FY17	FY18	FY19
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA4 CA4 CONTAMINATION AVOIDANCE (ACD&P)	RDTE	BA4	BA4	1	1953	0	0	0	0	0	0	0	0
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA5 CA5 CONTAMINATION AVOIDANCE (SDD)	RDTE	BA5	BA5	1	49021	20290	14681	2000	0	0	0	0	0
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	BA7 CA7 CONTAMINATION AVOIDANCE OPERATIONAL SYS DEV	RDTE	BA7	BA7	1	0	0	0	0	3000	5000	5000	5000	11252
New Plan	CA	CBRN DRS	CBRN DISMOUNTED RECONNAISSANCE SYSTEMS	P MC0101 CBRN DISMOUNTED RECONNAISSANCE SYSTEMS (CBRN DRS)	PROC	P	GP2000	1	12553	6624	15080	34998	81258	98272	105000	120326	0

<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.