# Calculating the difference between two rows of pivot table data

This is a discussion on Calculating the difference between two rows of pivot table data within the PowerPivot Questions forums, part of the Question Forums category; I have budget data that has 5 years of planned budgets for each program line in my portfolio that I ...

1. ## Calculating the difference between two rows of pivot table data

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)

2. ## Re: Calculating the difference between two rows of pivot table data

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.
Originally Posted by 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)

3. ## Re: Calculating the difference between two rows of pivot table data

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.

4. ## Re: Calculating the difference between two rows of pivot table data

Originally Posted by 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.
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

 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

5. ## Re: Calculating the difference between two rows of pivot table data

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

6. ## Re: Calculating the difference between two rows of pivot table data

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

Originally Posted by NorthupL
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

 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

7. ## Re: Calculating the difference between two rows of pivot table data

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

8. ## Re: Calculating the difference between two rows of pivot table data

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.

9. ## Re: Calculating the difference between two rows of pivot table data

sorry, repeat post

10. ## Re: Calculating the difference between two rows of pivot table data

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•