Forum Discussion
Display dynamic column header in a Matrix Visual
Hello,
I’m trying to build a financial report in Power BI Desktop and I’ve run into an issue:
I created a calculation group with several calculated items that return each scenario (Actual, Budget, etc.) as well as a variance in amount and percentage (which changes based on the slicer selection).
I’m trying to make the word “Variance” change according to each selection, but I’ve tried several approaches and haven’t succeeded, since I'm now learning that SELETEDVALUE() doesn't work as I was hoping.
Here’s what I currently have:
Scenario Group =
SWITCH(
TRUE(),
'Reviewed Scenarios'[Scenario] IN { "Actual", "Budget", "Forecast", "Prior Year" }, "Scenario",
'Reviewed Scenarios'[Scenario] IN {"Amount", "Percentage"}, "Variance",
BLANK()
)Here's the result Im trying to achieve:
Hi carolrt
It is not currently to dynamically change column headers. You will need a disconnected table similar to below and return their corresponding values using a conditional mesure. You will also need another slicer for the variance to determine which among the variance scenario is to be shown.
It would be easier to provide a solution if you provided a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
Hi carolrt
Can you change your disconnected table to belowHeader Scenario_Order Metrics Metrics_Order Scenario 1 Actual 1 Scenario 1 Budget 2 Scenario 1 Forecast 3 Forecast vs Budget 2 Actual 1 Forecast vs Budget 2 Budget 2 Forecast vs Budget 2 Forecast 3 Now create a matrix visual and add both columns(Header and Metrics) in columns field and
you can use selectedvalue dax to manipulate the output value:
Scenario Group = VAR selected_header=selectedvalue('Reviewed Scenarios'[Header]) VAR selected_metric=selectedvalue('Reviewed Scenarios'[Metric]) SWITCH( TRUE(), ,selected_header= "Scenario" && selected_metric="Actual",[Measure1] //put your measures ,selected_header= "Scenario" && selected_metric="Budget",[Measure2] //put your measures, ,selected_header= "Scenario" && selected_metric="Forecast",[Measure3] //put your measures ,selected_header= "Forecast vs Budget" && selected_metric="Actual",[Measure4] //put your measures ,selected_header= "Forecast vs Budget" && selected_metric="Budget",[Measure5] //put your measures ,selected_header= "Forecast vs Budget" && selected_metric="Forecast",[Measure6] //put your measures, selected_header IN {"Amount", "Percentage"}, "Variance", BLANK() )Please customize the code and table as per requirements.
Please give Kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
5 Replies
Hi carolrt
It is not currently to dynamically change column headers. You will need a disconnected table similar to below and return their corresponding values using a conditional mesure. You will also need another slicer for the variance to determine which among the variance scenario is to be shown.
It would be easier to provide a solution if you provided a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
Hi carolrt
It’s a bit hard to understand exactly what you’re trying to achieve, but in general, dynamic column headers in a Matrix aren’t natively supported.
To simulate this behavior, you can use an unrelated table or Field Parameters as a workaround.
Here are a few guides that can help:https://www.youtube.com/watch?v=BLNqU2jJ7u0
https://www.youtube.com/watch?v=Wzv9KMaIlcU
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi carolrt
Can you change your disconnected table to belowHeader Scenario_Order Metrics Metrics_Order Scenario 1 Actual 1 Scenario 1 Budget 2 Scenario 1 Forecast 3 Forecast vs Budget 2 Actual 1 Forecast vs Budget 2 Budget 2 Forecast vs Budget 2 Forecast 3 Now create a matrix visual and add both columns(Header and Metrics) in columns field and
you can use selectedvalue dax to manipulate the output value:
Scenario Group = VAR selected_header=selectedvalue('Reviewed Scenarios'[Header]) VAR selected_metric=selectedvalue('Reviewed Scenarios'[Metric]) SWITCH( TRUE(), ,selected_header= "Scenario" && selected_metric="Actual",[Measure1] //put your measures ,selected_header= "Scenario" && selected_metric="Budget",[Measure2] //put your measures, ,selected_header= "Scenario" && selected_metric="Forecast",[Measure3] //put your measures ,selected_header= "Forecast vs Budget" && selected_metric="Actual",[Measure4] //put your measures ,selected_header= "Forecast vs Budget" && selected_metric="Budget",[Measure5] //put your measures ,selected_header= "Forecast vs Budget" && selected_metric="Forecast",[Measure6] //put your measures, selected_header IN {"Amount", "Percentage"}, "Variance", BLANK() )Please customize the code and table as per requirements.
Please give Kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Hi carolrt,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to Praful_Potphode , Ritaf1983 and danextian for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarHi carolrt,
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna Kumar
