Forum Discussion
Unable to sort matrix visual values by month
Hi there,
I have a matrix visual where I am displaying sales values by month (columns) for a list of customers (rows). I have a relationship between my sales table and my calendar table, so I am able to use a single measure for the sales values and have it display by month.
I have the requirement to allow sorting by sales for selected months. E.g., if I click on January, I want to be able to sort by sales in January. If I click February, it should sort by sales in February, etc.
Following some online examples, I created a sorting measure, which is intended to identify the selected month, and sort by the sales for that month. However, it does not seem to pick up the selected month value correctly, and it always defaults to sorting by the total for the year.
Here is my measure (with hard-coded date values for testing purposes):
On my visual, I added this measure and I can see that it is correctly assigning a rank value, but it does not sort by that month correctly when I selected to sort the visual by that field:
I am displaying the SELECTEDVALUE of the calendar table in a card, and I can see that it shows the selected month and is filtered, but this doesn't seem to be evaluating properly in my measure:
If I hardcode the value in my measure, it works:
Visual:
Can anyone tell me what I am doing wrong? It's SO close, but I just can't get it to dynamically recognize the selected 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
4 Replies
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 KarmaniHi Shai,
Thank you very much! I used a slicer and it is now sorting correctly.
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.
