Forum Discussion

lucadiielsi's avatar
lucadiielsi
Frequent Visitor
7 years ago
Solved

Sorting matrix values

Hi everybody,   I've been stuck for days with a problem with matrix visual. It's a very simple issue: I've got a matrix with some product names (let's say A, B and C) as rows, months as columns and...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago

Hello lucadiielsi ,

The only way I could find to do this is pretty ugly.

1. Add a table with the month names that is NOT connected to the date table.  This is just for the users to select the 'Sort by' month.

Months = SUMMARIZECOLUMNS('Date'[Month],'Date'[Month Number])

You can set the sorting on the new Months table to sort the month column on the month number column.

Then you add a measure that reads the selected month and only returns your value for that month or the total if no month is selected.

Sort Measure = 
VAR SelectedMonth = SELECTEDVALUE(Months[Month])
RETURN 
IF ( ISBLANK(SelectedMonth), [Sales Amount],
CALCULATE([Sales Amount],FILTER( 'Date', 'Date'[Month] = SelectedMonth ) ) )

Add a drop down of the months from the table you created, this is for the users to pick the sort order.

Then you add the sort measure to your matrix and this is where it gets really ugly.  It will add the sort measure under every month, even if you pick a sort month, all but one months of values will be blank but the space will still be there.  You just have to set all the column widths for the sort measure month to 0.  Then you set the sort on the matrix to the sort measure.