Add 2nd column header to Matrix

[EMP]

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):

What I'm after:

As always, any help appreciated.

Thanks,

Emily

[EMP]

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]

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

[PaulDBrown]

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:

[EMP]

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]

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

[EMP]

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 · Accepted solution]

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]

@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

[EMP]

@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

[amitchandak]

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

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