Forum Discussion
Add 2nd column header to Matrix
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
You can download a free (legit: it's from the same developer) from
As for COALESCE you'll have to try it in this case and see if it's efficient enough or just use the measure you already have of course.
As I mentioned in a follow up post, you can actually create the structure you posted by creating a "custom header" and a bit of DAX magic
Thanks PaulDBrown .
Would you mind elaborating on the "custom column and DAX magic"? I'd prefer that solution over conditional formatting. Also my IT department is - on the extreme side - of what we can and cannot install on our PCs, so a solution that doesn't require another program is very appealing.
Thanks,
Emily
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