Forum Discussion
Comparing Values from One Column in Same Table
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!
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
6 Replies
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 - __AGreg_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"?
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
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.
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.
