PowerPivot to compare two sets of summarized data

mountainman88

New Member
Joined
Jun 22, 2019
Messages
35
I have two sets up data which will always have identical column headings held in Table A and Table B. The data is run from the same system on two different dates to create the two data sets. The contents of the rows will change and although they do relate there is no unique identifier to link them. New rows can be added between A and B dates, rows can be removed, or existing Rows can have data changed. Below is very simplified example:

Table ATable B
Initiative NumPLFY20Initiative NumPLFY20
1Revenue1,0001Revenue1,200
1Revenue5,0001COS4,800
1SGA2,0001SGA2,200
2COS8,0002COS10,000
2SGA9,0002SGA6,000
3Revenue4,0003Revenue2,000
3COS6,0003COS8,000
4SGA4,0004SGA3,500
5Revenue1,2005Revenue2,200
5COS5,8005COS6,000
6COS2,500
6COS6,000
7SGA7,000
46,00061,400


What I currently have to do is run SUMIFS() on both sets of data and then using the summarized data create the analysis below:

Analysis
Initiative NumPLABDelta (A-B)
1Revenue6,0001,2004,800
1COS-4,800(4,800)
1SGA2,0002,200(200)
2Revenue---
2COS8,00010,000(2,000)
2SGA9,0006,0003,000
3Revenue4,0002,0002,000
3COS6,0008,000(2,000)
3SGA---
4Revenue---
4COS---
4SGA4,0003,500500
5Revenue1,2002,200(1,000)
5COS5,8006,000(200)
5SGA---
6Revenue---
6COS-8,500(8,500)
6SGA---
7Revenue---
7COS---
7SGA-7,000(7,000)
46,00061,400(15,400)


Now in reality the data is far more complicated than this example and I have found that using a pivot table on the individual Tables is the only practical way to navigate them. Recreating the SUMIFs each time is a pain because I have to maintain multiple combinations of dimensions that may never have data in them. This is why a pivot table is ideal, but so far I can just pivot Table A or Table B, not a set of calcs between them.

I understand I can create a separate table with every combination of Initiative and PL column and bring in summarized data from both tables, then create excel calcs and pivot that data. But I want to avoid that level of maintenance.

I have been looking at PowerQuery and PowerPivot as a solution to this issue. My question is: is there away to setup connections to the two tables as data sources, summarise them and then create calculations between the two summarized Data tables per the Delta (A-B) calculation above, and then pivot THAT data? Maybe I need to maintain a separate relational table that lists every initiative number individually, and this can create the link between the two data sets somehow, and then use Dax expressions for the calcs?
 
Last edited:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows
With Power Query, Bring each table into the Editor. Group the Rows on the Initiative and PL to get a sum of each category for each table. Then do a full join (merge) the two tables. After merging, here is the Mcode to get the expected results.

Rich (BB code):
let
    Source = Table.NestedJoin(TableA, {"Initiative Num", "PL"}, TableB, {"Initiative Num", "PL"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Initiative Num", "PL", "Sum FY20"}, {"TableB.Initiative Num", "TableB.PL", "TableB.Sum FY20"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "A.FY20", each if[Sum FY20]= null then 0 else [Sum FY20]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "B.FY20", each if [TableB.Sum FY20]=null then 0 else [TableB.Sum FY20]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Sum FY20", "TableB.Sum FY20"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [A.FY20] - [B.FY20], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Difference"}})
in
    #"Renamed Columns"

Book4
ABCDEFG
1Initiative NumPLTableB.Initiative NumTableB.PLA.FY20B.FY20Difference
21Revenue1Revenue600012004800
31SGA1SGA20002200-200
41COS04800-4800
52COS2COS800010000-2000
62SGA2SGA900060003000
73Revenue3Revenue400020002000
83COS3COS60008000-2000
94SGA4SGA40003500500
105Revenue5Revenue12002200-1000
115COS5COS58006000-200
126COS08500-8500
137SGA07000-7000
Sheet2
 

mountainman88

New Member
Joined
Jun 22, 2019
Messages
35
So this is really great but the real data im using is far more detailed and although I'd like to learn PowerPivot detailed mcode is beyond me (although I can manage basic stuff like arithmetic and text manipulation). The merged table works with your code im sure but I see that you have to jump from columns. Is there a way to have the two merged tables as separate data sources and go from there?

I have managed to create two tabs for Table A and Table B in Power query and grouped the data by Initiative Num and PL. I created a separate table which is just the initiative numbers and managed to get the Table A and Table B totals (Revenue+COS+SGA) for FY20 to come through by linking a pivot of the 3 tables.

The issue I have is when I try and bring in the PL category totals separately they are wrong. How would I make it work just using tables and making relationships between them? I tried creating a unique ID column for each data set which is =[initiaitve Num]&[PL] but it wouldnt let me me link them?

Thanks
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows
I'm a visual type and am having a hard time following your explanation. Can you show visually what you have and what you want. Don't understand why you cannot join the unique ID that you have created. So long as they are the same in each table there should be no issue.
 

mountainman88

New Member
Joined
Jun 22, 2019
Messages
35

ADVERTISEMENT

The UID method wouldn’t be full outer even if it did work right?

I will try and play with it this weekend.
 

mountainman88

New Member
Joined
Jun 22, 2019
Messages
35
With Power Query, Bring each table into the Editor. Group the Rows on the Initiative and PL to get a sum of each category for each table. Then do a full join (merge) the two tables. After merging, here is the Mcode to get the expected results.

Rich (BB code):
let
    Source = Table.NestedJoin(TableA, {"Initiative Num", "PL"}, TableB, {"Initiative Num", "PL"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Initiative Num", "PL", "Sum FY20"}, {"TableB.Initiative Num", "TableB.PL", "TableB.Sum FY20"}),
    #"Added Custom" = Table.AddColumn(#"Expanded TableB", "A.FY20", each if[Sum FY20]= null then 0 else [Sum FY20]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "B.FY20", each if [TableB.Sum FY20]=null then 0 else [TableB.Sum FY20]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Sum FY20", "TableB.Sum FY20"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [A.FY20] - [B.FY20], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Difference"}})
in
    #"Renamed Columns"

Book4
ABCDEFG
1Initiative NumPLTableB.Initiative NumTableB.PLA.FY20B.FY20Difference
21Revenue1Revenue600012004800
31SGA1SGA20002200-200
41COS04800-4800
52COS2COS800010000-2000
62SGA2SGA900060003000
73Revenue3Revenue400020002000
83COS3COS60008000-2000
94SGA4SGA40003500500
105Revenue5Revenue12002200-1000
115COS5COS58006000-200
126COS08500-8500
137SGA07000-7000
Sheet2

The simple thing that would get me the result I'm looking for: is there a way using your example, to output column A and C as a single column with no nulls, and the same for columns B&D like below:

Initiative Num (A and B merge)PL (A & B merge but favor B)A.FY20B.FY20
1Revenue60001200
1SGA20002200
1COS04800
2COS800010000
2SGA90006000
3Revenue40002000
3COS60008000
4SGA40003500
5Revenue12002200
5COS58006000
6COS08500
7SGA07000


Is there a way to do this that doesnt involve lots of Mcode? perhaps by setting up relationships with other tables I could create?

thanks
 

mountainman88

New Member
Joined
Jun 22, 2019
Messages
35
So this is where Ive got to, I have three tabs in the data model.

1) A single column list of every initiative number from Table A and B using append and remove duplicates
2) Table A grouped by initiative and PL
3) Table B grouped by initiative and PL

I feel I'm close here, somehow I need to introduce the PL split into this
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows
Rich (BB code):
let
    Source = Table.NestedJoin(TableA, {"Initiative Num", "PL"}, TableB, {"Initiative Num", "PL"}, "TableB", JoinKind.FullOuter),
    #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Initiative Num", "PL", "FY20"}, {"TableB.Initiative Num", "TableB.PL", "TableB.FY20"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded TableB",null,0,Replacer.ReplaceValue,{"FY20"}),
    #"Inserted Subtraction" = Table.AddColumn(#"Replaced Value", "Subtraction", each [FY20] - [TableB.FY20], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Initiative Num", "PL"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"TableB.Initiative Num", "TableB.PL", "FY20", "TableB.FY20", "Subtraction"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"TableB.Initiative Num", "Initiative"}, {"TableB.PL", "PL"}, {"FY20", "A"}, {"TableB.FY20", "B"}, {"Subtraction", "Delta (A-B)"}})
in
    #"Renamed Columns"

Book1
ABCDE
1InitiativePLABDelta (A-B)
21Revenue10001200-200
31Revenue500012003800
41COS04800-4800
51SGA20002200-200
62COS800010000-2000
72SGA900060003000
83Revenue400020002000
93COS60008000-2000
104SGA40003500500
115Revenue12002200-1000
125COS58006000-200
136COS02500-2500
146COS06000-6000
157SGA07000-7000
Sheet2

 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,576
Members
410,852
Latest member
WernerS
Top