Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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 @bchager6 - 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.
@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
@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"?
@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
Thanks Mate - That worked.
Cheers for your help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |