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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
gghlyon
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
mattneil
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

3 REPLIES 3
mattneil
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

 

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.