cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dynamic Column Names for Matrix Visual using Sliced Measures

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.  

P&L.png
 
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

1 ACCEPTED SOLUTION
Frequent Visitor

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

2019-09-12_11-20-36.png

 

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

 

View solution in original post

2 REPLIES 2
Frequent Visitor

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

2019-09-12_11-20-36.png

 

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

 

View solution in original post

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors