Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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 Power BI 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. #1
    New Member
    Join Date
    Aug 2012
    Posts
    4

    Question 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. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default 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.
    Quote Originally Posted by NorthupL View Post
    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. #3
    Board Regular
    Join Date
    Jan 2012
    Location
    Ohio
    Posts
    239

    Default 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. #4
    New Member
    Join Date
    Aug 2012
    Posts
    4

    Default Re: Calculating the difference between two rows of pivot table data

    Quote Originally Posted by powerpivotpro View Post
    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. #5
    New Member
    Join Date
    Aug 2012
    Posts
    4

    Default 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. #6
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,924

    Default 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

    Quote Originally Posted by NorthupL View Post
    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. #7
    Board Regular
    Join Date
    Aug 2012
    Location
    Charlotte, NC
    Posts
    65

    Default 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. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default 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. #9
    Board Regular
    Join Date
    Feb 2012
    Location
    Indianapolis, IN
    Posts
    188

    Default Re: Calculating the difference between two rows of pivot table data

    sorry, repeat post

  10. #10
    New Member
    Join Date
    Aug 2012
    Posts
    4

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com