Forum Discussion
Dynamically Sort Measure Columns Based on Month Slicer
Hi there,
I have a table visual where columns are measures. Is there a way to dynamically sort or reorder those columns based on their total values, with the ranking changing according to a month slicer selection?
For example, if column A has the highest total in September, it would appear as the first column, but if column B has the highest total in October, it would move to the first column automatically.
Has anyone implemented something similar or come across a workaround? Any ideas or suggestions would be greatly appreciated.
Regards,
Hi myl77 ,
There is no easy way to do this because the columns are based on text that you present and the sorting is on the rows not on the columns.
There are some work around based on adding a disconnected table and a custom measure, however taking your definition into account that you should have this respond to the month slicer believe that will generate a table with a large amount of data so basically one value per month / year per measure that will then allow you to do the sorting column.
Check the two links below with some explanation on having this sorting however be aware that this is not exactly what you need
https://insurancedatainsights.blog/2024/04/27/dynamic-data-display-matrix/
https://www.youtube.com/watch?v=k5FMfvPmkv4&t=190s
If you can share a mockup of the data along with answering the following questions, I can try and get an working sample for you.
- How many measures do you have in the matrix
- How many Years / Months is your data
Regards
Miguel Félix
5 Replies
v-kathullacCommunity Support
Hi myl77 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya- v-kathullac
Hi myl77 ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
Regards,
Chaithanya - MFelix
Super User
Hi myl77 ,
There is no easy way to do this because the columns are based on text that you present and the sorting is on the rows not on the columns.
There are some work around based on adding a disconnected table and a custom measure, however taking your definition into account that you should have this respond to the month slicer believe that will generate a table with a large amount of data so basically one value per month / year per measure that will then allow you to do the sorting column.
Check the two links below with some explanation on having this sorting however be aware that this is not exactly what you need
https://insurancedatainsights.blog/2024/04/27/dynamic-data-display-matrix/
https://www.youtube.com/watch?v=k5FMfvPmkv4&t=190s
If you can share a mockup of the data along with answering the following questions, I can try and get an working sample for you.
- How many measures do you have in the matrix
- How many Years / Months is your data
Regards
Miguel Félix
jubinsoniSolution Sage
Hi myl77, one workaround to consider is to flip the layout so the measure names become rows. Create a small disconnected table with one row per measure name, then write one measure that uses SWITCH on the selected row name to return the matching measure value for the month in the slicer. Add a second measure that returns that value for sorting, and sort the visual by it descending. Rows can be sorted by a measure at run time, so the highest total for the selected month will automatically come first. If you really need them as columns, a matrix with the measure name table on columns can show the same order only if the sort is applied through a rank column, which is harder to keep dynamic, so the transposed version is usually the more reliable option.
- ShivekMaharaj
Community Champion
Hi myl77,
The existing replies are right that Power BI does not natively reorder separate measure columns dynamically based on their evaluated values.
One useful distinction is that field parameters can dynamically control which measures are shown, but their order is still driven by the parameter itself, not by a DAX ranking that changes with the month slicer.
Microsoft documents field parameters here in the Field parameters guidance.
So if the requirement is specifically:
September -> Measure A ranks highest -> move A to first column October -> Measure B ranks highest -> move B to first columnthe native table/matrix does not currently support that kind of runtime measure-column reordering directly.
The more practical workaround is still to turn the measures into rows using a disconnected measure table and one SWITCH-based measure, because rows can be sorted dynamically by another measure.
That gives you:
Measure Name Current Month Value Rankand then you can sort by Rank or Value based on the selected month.
If you absolutely need the measures to remain as columns, a custom visual such as Deneb gives you more control over the rendered order, but for a native Power BI visual I would model them as rows instead.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
