Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EMP
Advocate II
Advocate II

Add 2nd column header to Matrix

Greetings. Dealing with budgets and actuals.

 

I've got a user that requested a matrix that shows 12 months of data. If actuals are available the column displays actuals. If the actuals are not available then the budget for that period is displayed. I created a measure "BudgetOrActual" that sums the budget if the sum of actual = 0 and dropped it into the matrix as the value, works great. The problem I am having is alerting the user visually if the numbers displayed in the column are actual or budget numbers. 

 

I created a 2nd measure to bring me the words budget or actual; if actual = 0 then say budget otherwise say actual... but I can't add that measure to the matrix as a 'top fact row' under the column headers on the matrix. Any ideas?

 

What I have now (recreated in Excel to protect sensitive data):

Now.PNG

What I'm after:

Want.PNG

 

As always, any help appreciated.

 

Thanks,

Emily

 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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

pq.jpg

 

2) Set up a relationship between the month fields:

model.jpg

 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

matrix setup.jpg

 

I've attached the sample PBIX file for you





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
EMP
Advocate II
Advocate II

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

PaulDBrown
Community Champion
Community Champion

If you wish to have the second header, it can be accomplished, albeit with a little bit of work...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

You could resort to conditional formatting to identify the budget values:

In my example:

 

Matrix value =
SUMX ( 'Table', COALESCE ( [Sum Actuals], [Sum Budget] ) )

 

and for the formatting:

 

Format = 
IF([Matrix value] = [Sum Budget], 1)

 

and then apply the conditional formatting using the rules option to get:

budget.jpg

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






That's a good idea! I'm working with my IT team now to try and get the open source version of Tabular, but if that doesn't work out for me I'll likely end up resorting to this. 

Question though: the first, and last, time I used COALESCE in a model (few days ago) it really slowed down the load time of the visual.... un-usable basically, so I had to remove it. I didn't really find much info on that issue when I Googled it. Seeing as you are recommending it, have you ever heard of/experienced that issue? 

 

Thanks,

Emily

PaulDBrown
Community Champion
Community Champion

You can download a free (legit: it's from the same developer) from 

Tabular Editor 2x 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

PaulDBrown
Community Champion
Community Champion

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

pq.jpg

 

2) Set up a relationship between the month fields:

model.jpg

 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

matrix setup.jpg

 

I've attached the sample PBIX file for you





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User

@EMP , create a calculation group on two measure and use that- it will work like a dimension

 

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

 

or

 

Calculation Groups - Use Measure with small multiple : https://www.youtube.com/watch?v=LEn6ZnScMZc

@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

@EMP , I think tabular editor 2 is open source. Can you check that with IT

https://www.sqlbi.com/tools/tabular-editor/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.