Forum Discussion
Add 2nd column header to Matrix
- 4 years ago
I've edited this post since the model works with an active relationship between the tables
Sure, here goes.
1) You need to create a table which will allow for the column structure of the matrix. This you can create in Power Query which will furthermore make it dynamic. Basically you need a table referenced to the fact table and add a conditional column which returns "Budget" if the row for "Actuals" is blank else "Actuals"
This is the M-code I've used
let Source = Table, #"Removed Columns" = Table.RemoveColumns(Source,{"dimension", "Budget"}), #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Calculation", each if [Actuals] = null then "Budget" else "Actuals"), #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Actuals"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns1") in #"Removed Duplicates"to get
2) Set up a relationship between the month fields:
3) create simple base measures for actuals and budget
Sum of Actuals = SUM(Table[Actuals])Sum of Budget = SUM(Table[Budget])4) Create the final measure you will be using in the matrix
Final matrix measure = SWITCH ( SELECTEDVALUE ( 'Matrix Header'[Calculation] ), "Actuals", [Sum of Actuals], "Budget", [Sum of Budget, SUMX ( 'Table', IF ( ISBLANK ( [Sum Actuals] ), [Sum Budget], [Sum Actuals] ) ) )5) finally set up the matrix visual using the whatever field for the rows, the fields from the 'Matrix Header' table as columns and [Final matrix measure] as the values. Format the visual to your liking
I've attached the sample PBIX file for you
amitchandak - appreciate your response. If I am understanding this solution correctly it requires Tabular Editor, which is something my company does not have (and is unlikely to purchase at this time).
Any other ideas?
Thanks,
Emily
Anonymous , I think tabular editor 2 is open source. Can you check that with IT