Dynamic Column Names for Matrix Visual using Sliced Measures

[gghlyon]

Hello,
I have built a P&L report, and have a question regarding the column headers in a matrix, whether these can be set dynamically.  Let me set the scene.
 
The measures in the matrix all work with a mapping table so can show aggregates of data (revenue) or measures (GP %) so i could fit everything in the one visual and have drilldown.  

 
The 'Actual P&L' measure column is the actual result.  I have very similar 'Budget P&L', 'PM P&L', 'PY P&L' measures which look up different tables or offset date ranges.  On this particular tab the users want to see how these things compare, so i have yet another measure 'Variant P&L' that presents one of the other P&L results depending on how the 'Variant' slicer is set.  Elegant if a bit slow.
 
The problem is that then locks the name of the column header for 'Variant P&L' and my users want to see what it actually is.
 
 
I have set the title of the matrix dynamically depending on what's sliced from the Variant/Compare slicers (created a measure to write the title name based on this) but that's not good enough.  I would really prefer to change the column headers (so in the example picture the column headed 'Variant P&L' would say 'Budget P&L' since we're slicing to look at the budget figure. 
 
Only options i can think of apart from what i've done is;
I could maybe add another line in the mapping table (above revenue) and have the first row of the table faking as the header (and blank the real header out). 
I could have 9 separate matrix visuals and buttons to hide/show them for all the permutations (Budget/PM, Budget/PY, Forecast/PM etc.) and then can hard rename the column headers in each matrix however i like.  But then have to maintain 9 visuals instead of one.
 
Any other ideas?
 
Many thanks,
Guy

[mattneil · Accepted solution]

Hi @gghlyon 

I've got an option that hopefully won't need much rework in your report

Create a mapping table which has the combinations of headers you want for the selection of your comparison in the slicer

Then in your matrix put TableHeaders in the Columns.

Finally, you'll need to create a "master" measure for use in the Values section of the matrix visual.  This measure makes use of the SWITCH function to show the relevant values for the column header.  You're essentially mapping your desired column header names to your existing Measures in this formula

MatrixValues = SWITCH(MAX(Mappings[TableHeaders]),
                "Actual",[Actuals],
                "Budget",[Budgets],
                "Forecast",[Forecasts],
                "Var Act vs Fcst",[Actuals] - [Forecasts],
                "Var Act vs Bud",[Actuals] - [Budgets])

I've attached a prototype as a .pbix here.  Inspiration from Matt Allington's excellent article on using SWITCH in a matrix

Hope that helps, let me know how it goes for you

Cheers,

Matt

[Oceans88]

This is great info thank you, but how can i format the columns individually to make this work. Example, if i have a percentage column i would like to format that as %.

Thank you for your help

[astanfo]

@Oceans88 Have you found a solution with this? @mattneil Is there a solution to this if you would like multiple formats?