Forum Discussion
Assistance Needed with Matrix Table Sorting
Hi All,
I hope you're doing well.
I need some help with sorting columns in a matrix visual. I'm currently using the following measure to display values:
Measure Value =
VAR SelectedDate = SELECTEDVALUE('Dim_Calendar'[Date], MAX('Dim_Calendar'[Date]))
RETURN
SWITCH(
TRUE(),
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = "Contr.", FORMAT([% Channel Sales Contribution], "0.00%"),
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, 0), "MMM-YY"), [CM Sales],
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, -1), "MMM-YY"), [LM Sales],
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, -2), "MMM-YY"), [Last 2M Sales],
SELECTEDVALUE('Sales Dynamic Measure Table'[Month & Year]) = FORMAT(EOMONTH(SelectedDate, -3), "MMM-YY"), [Last 3M Sales],
BLANK()
)
----------------------------------------------------------------------------------------------------------------------------------
In the Year & Month slicer, I’ve selected Apr-25, and I want the matrix columns to appear in the following order:
Contr. | Jan-25 | Feb-25 | Mar-25 | Apr-25
However, currently the sorting is happening alphabetically, which is not what I want.
Could someone please guide me on how to apply a custom or dynamic sort to achieve the desired order?
Thanks in advance!
BR,
Vannur Vali
Hi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Keep your Sales Dynamic Measure Table as it is. It already generates the Month & Year values you need, including fixed KPIs like “Contr.” and “LY”. Add a new Sort Order calculated column that adjusts based on the selected month in your slicer.
Example:
Sort Order =
VAR SelectedDate = SELECTEDVALUE(Dim_Calendar[Date], MAX(Dim_Calendar[Date]))
VAR SelectedMonthYear = FORMAT(EOMONTH(SelectedDate, 0), "MMM-YY")
VAR Month4Prior = FORMAT(EOMONTH(SelectedDate, -4), "MMM-YY")
VAR Month3Prior = FORMAT(EOMONTH(SelectedDate, -3), "MMM-YY")
VAR Month2Prior = FORMAT(EOMONTH(SelectedDate, -2), "MMM-YY")
VAR Month1Prior = FORMAT(EOMONTH(SelectedDate, -1), "MMM-YY")
RETURN
SWITCH(
TRUE(),
'Sales Dynamic Measure Table'[Month & Year] = "Contr.", 1,
'Sales Dynamic Measure Table'[Month & Year] = Month4Prior, 2,
'Sales Dynamic Measure Table'[Month & Year] = Month3Prior, 3,
'Sales Dynamic Measure Table'[Month & Year] = Month2Prior, 4,
'Sales Dynamic Measure Table'[Month & Year] = Month1Prior, 5,
'Sales Dynamic Measure Table'[Month & Year] = SelectedMonthYear, 6,
'Sales Dynamic Measure Table'[Month & Year] = "LY", 7,
'Sales Dynamic Measure Table'[Month & Year] = "YTD", 8,
'Sales Dynamic Measure Table'[Month & Year] = "QTD", 9,
'Sales Dynamic Measure Table'[Month & Year] = "Vs LM", 10,
'Sales Dynamic Measure Table'[Month & Year] = "% Vs LM", 11,
'Sales Dynamic Measure Table'[Month & Year] = "% Vs LY", 12,
'Sales Dynamic Measure Table'[Month & Year] = "% Vs YTD", 13,
999
)
Once that’s in place, go to the Data view, select the Month & Year column and set Sort by Column -> Sort Order. This ensures your matrix columns update automatically based on slicer selection, without circular references.
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
7 Replies
Hi rajendraongole1,
Thank you for the quick response.
I’ve created a new calculated table called "Month & Year"
Sales Dynamic Measure Table = UNION(DISTINCT(Dim_Calendar[Month & Year]) , ROW("Month & Year", "Contr."), ROW("Month & Year", "LY"), ROW("Month & Year", "YTD"), ROW("Month & Year", "QTD"), ROW("Month & Year", "Vs LM"), ROW("Month & Year", "% Vs LM"), ROW("Month & Year", "% Vs LY"), ROW("Month & Year", "% Vs YTD"))
I followed your suggestion and created a calculated column as you described, but I'm encountering the following error:Could you please help me resolve this? Thanks in advance!
BR,
ValiHi Anonymous , Thank you for reaching out to the Microsoft Community Forum.
Keep your Sales Dynamic Measure Table as it is. It already generates the Month & Year values you need, including fixed KPIs like “Contr.” and “LY”. Add a new Sort Order calculated column that adjusts based on the selected month in your slicer.
Example:
Sort Order =
VAR SelectedDate = SELECTEDVALUE(Dim_Calendar[Date], MAX(Dim_Calendar[Date]))
VAR SelectedMonthYear = FORMAT(EOMONTH(SelectedDate, 0), "MMM-YY")
VAR Month4Prior = FORMAT(EOMONTH(SelectedDate, -4), "MMM-YY")
VAR Month3Prior = FORMAT(EOMONTH(SelectedDate, -3), "MMM-YY")
VAR Month2Prior = FORMAT(EOMONTH(SelectedDate, -2), "MMM-YY")
VAR Month1Prior = FORMAT(EOMONTH(SelectedDate, -1), "MMM-YY")
RETURN
SWITCH(
TRUE(),
'Sales Dynamic Measure Table'[Month & Year] = "Contr.", 1,
'Sales Dynamic Measure Table'[Month & Year] = Month4Prior, 2,
'Sales Dynamic Measure Table'[Month & Year] = Month3Prior, 3,
'Sales Dynamic Measure Table'[Month & Year] = Month2Prior, 4,
'Sales Dynamic Measure Table'[Month & Year] = Month1Prior, 5,
'Sales Dynamic Measure Table'[Month & Year] = SelectedMonthYear, 6,
'Sales Dynamic Measure Table'[Month & Year] = "LY", 7,
'Sales Dynamic Measure Table'[Month & Year] = "YTD", 8,
'Sales Dynamic Measure Table'[Month & Year] = "QTD", 9,
'Sales Dynamic Measure Table'[Month & Year] = "Vs LM", 10,
'Sales Dynamic Measure Table'[Month & Year] = "% Vs LM", 11,
'Sales Dynamic Measure Table'[Month & Year] = "% Vs LY", 12,
'Sales Dynamic Measure Table'[Month & Year] = "% Vs YTD", 13,
999
)
Once that’s in place, go to the Data view, select the Month & Year column and set Sort by Column -> Sort Order. This ensures your matrix columns update automatically based on slicer selection, without circular references.
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi Anonymous ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!Hi Anonymous , Thank you for reaching out to the Microsoft Fabric Community Forum.
I have reproduced the scenario using sample data and it worked successfully on my end.
Outcome:
I am also including the .pbix file for your better understanding. Please review it:If this post helps, please give us 'Kudos' and consider accepting it as a solution to help other members find it more quickly.
Hi Anonymous , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!Hi Anonymous - You’ll need to add a new column in Sales Dynamic Measure Table that assigns a numeric sort order for each Month & Year value.
Sort Order =
SWITCH(
TRUE(),
'Sales Dynamic Measure Table'[Month & Year] = "Contr.", 1,
'Sales Dynamic Measure Table'[Month & Year] = "Jan-25", 2,
'Sales Dynamic Measure Table'[Month & Year] = "Feb-25", 3,
'Sales Dynamic Measure Table'[Month & Year] = "Mar-25", 4,
'Sales Dynamic Measure Table'[Month & Year] = "Apr-25", 5,
999 -- fallback for unknown labels
)Now, select the Month & Year column in the Power BI Data view, and click:
Modeling tab → Sort by Column → Sort Order
Power BI will now use the Sort Order column to sort the Month & Year column, preserving the order you defined, instead of sorting alphabetically.
This helps. please check.
Hello Anonymous , Just getting back to see if the shared details answered your question. If so, marking it as "Accept as Solution" would be greatly appreciated to guide others in the community. Feel free to reach out with any additional questions!
