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
I'm still trying to get my IT department on board with Tabular Editor.... it is open source but they are not having it for reasons I haven't been told yet... maybe they will come around, maybe not...
In the meantime (and with a bunch of side help from him) I implemented a variation of PaulDBrown's solution and it's working great!
Truly appreciate everyone's help and time, this community is awesome!
Emily