Comparing Values from One Column in Same Table

[hackfifi]

Good Day!

I have the below Sample Data Table/Query "Detail". I would like to acheive the a table visual shown below ("Expected Result") to compare/reconcile values between two Projects. I can easily develop a "Matrix Visual" showing the values only, but cannot work out how to calculate the "Delta". 

Also kindly note "Project" & "Type" are also "Key" tables which has a relationship with "Detail" Table. The user should "only" select two projects. There are over 100 projects.  Cheers in advance!

[Anonymous]

I recreated your Data Table and saved it as Book12.xlsx and performed the below transformation in the query editor.

let
Source = Excel.Workbook(File.Contents("C:\Book12.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project", type text}, {"Type", type text}, {"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Project]), "Project", "Value", List.Sum),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"A", "B", "C"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Type", "Project"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"C"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Delta (A vs B)", each [B]-[A])
in
#"Added Custom"

Below is the resulting table.

Hope this helps.

[hackfifi]

Thanks @Anonymous  - Unfortunately, i cannot modify the table in power query as i have over 100 projects. I just provided a sample dataset; and there are over 70+ columns already and there are a lot of measure based on the current power query. I need to work out a "measure". Cheers again for your time.

[Greg_Deckler]

@hackfifi Seems like something along the lines of:

Delta (A vs B) measure =
  VAR __Type = MAX('Table'[Type])
  VAR __A = SUMX(FILTER('Table',[Type]=__Type && [Project] = "A"),[Value])
  VAR __B = SUMX(FILTER('Table',[Type]=__Type && [Project] = "B"),[Value])
RETURN
  __B - __A

[hackfifi]

@Greg_Deckler Thanks for taking time to respond. But my "Project" dropdown (list of 100+ projects) would be dynamic i.e. the user can select ANY two out of 100+ projects. I just provided sample dataset...the current data model has over 70+ columns. 
I am not sure if i need to have two project tables...so user can single select one project from each "Project" filter? i.e. Select Project "A" & Select Project "B"?
 

[Greg_Deckler · Accepted solution]

@hackfifi Ah, details are important. You will want two disconnected Project tables. You can create these using this code:

ProjectSlicerTable1 = DISTINCT('Table'[Project])

ProjectSlicerTable2 = DISTINCT('Table'[Project])

Use these for your slicers. The the code becomes:

Delta measure =
  VAR __Type = MAX('Table'[Type])
  VAR __Compare1 = MAX('ProjectSlicerTable1'[Project])
  VAR __Compare2 = MAX('ProjectSlicerTable2'[Project])
  VAR __A = SUMX(FILTER('Table',[Type]=__Type && [Project] = __Compare1),[Value])
  VAR __B = SUMX(FILTER('Table',[Type]=__Type && [Project] = __Compare2),[Value])
RETURN
  __B - __A

[hackfifi]

Thanks Mate - That worked. 

Cheers for your help