Forum Discussion
Unable to sort matrix visual values by month
The matrix does not pass the clicked column's filter context into the sort measure. When you sort a matrix by a measure, the engine evaluates that measure once per row using the full row context (effectively the row total, with all columns visible), so SELECTEDVALUE on Calendar[Month] returns blank and the measure falls back to the year total. That is the same behavior whether you click a column header or use "Sort by" in the visual menu.
The standard workaround is to drive the sort from a slicer or a field parameter rather than the column you click. Add a single select slicer with the month name, and have your sort measure read from that slicer:
Sort by Month = VAR _m = SELECTEDVALUE ( 'Sort Selector'[Month] ) RETURN CALCULATE ( [Sales], 'Calendar'[Month] = _m )
Then sort the matrix by [Sort by Month] descending. When the user picks a month in the slicer, the rows reorder by that month's sales.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
Power BI cannot sort a row if the Sort Order value changes as you move from left to right across the columns. By using ALLSELECTED, you ensure the Rank is a constant number across the entire row, allowing the visual to finally say, "Okay, I can sort this customer based on this specific value.
CustomerSalesLbs_SortValue_Actual =
VAR SelectedMonthForSorting =
CALCULATE(
SELECTEDVALUE(CalendarFilter[Month Name], "All"),
ALLSELECTED(CalendarFilter)
)
RETURN
SWITCH(SelectedMonthForSorting,
"January", RANKX(ALL(ArCustomerPlus[GlobalName]), CALCULATE([CustomerSalesLbs_Actual], SalHistorySource[InvoiceDate] >= DATEVALUE("2026-01-01") && SalHistorySource[InvoiceDate] < DATEVALUE("2026-02-01")),,ASC,Dense),
"February", RANKX(ALL(ArCustomerPlus[GlobalName]), CALCULATE([CustomerSalesLbs_Actual], SalHistorySource[InvoiceDate] >= DATEVALUE("2026-02-01") && SalHistorySource[InvoiceDate] < DATEVALUE("2026-03-01")),,ASC,Dense),
"All", RANKX(ALL(ArCustomerPlus[GlobalName]), CALCULATE([CustomerSalesLbs_Actual], SalHistorySource[InvoiceDate] >= DATEVALUE("2026-01-01") && SalHistorySource[InvoiceDate] < DATEVALUE("2027-01-01")),,ASC,Dense)
)
Thank you very much for the suggestions, but it did not resolve the issue. Per Shai's post, the matrix visual does not seem to pass the column's filter context, so I had to use a separate slicer.