Sorting matrix values

[lucadiielsi]

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 numbers as values (see the screenshot attached). Now, if I want to sort the values in that matrix, all I can do is choose to sort by the first column (products, alphabetical order) or by the last column (total, asc or desc). But what I need is to order the values by the other - dynamically created - columns: the months. I would like to be able to sort the whole matrix by the values of a specific month. 

Googling and reading the posts here in the community, I found out that PBI (at least to date) doesn't allow this feature, and there are also some ideas to vote about that.

So I'm here to ask for your help: do any of you know a trick to overcome the issue?

Thank you very much!

Luca

[rjhartop]

If anyone is still interested, this blog post provides a similar though possibly cleaner solution:

https://affinityworks.co.uk/power-bi-matrix-column-sort-sorted/

[jdbuchanan71]

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.

[lucadiielsi · Accepted solution]

Hi @jdbuchanan71,

first of all thanks for your quick reply.

Unfortunately I don't get some points of your solution. Below I attach a Dropbox link to the PBIX I used for some tests, would you be so kind to help me showing me how it goes?

Thanks!

Luca

https://www.dropbox.com/s/05fjnshdlu1gwbz/Test.pbix?dl=0

[jdbuchanan71]

Hello @lucadiielsi 

Here is an updated copy of the file with the date table and sorting measure.

https://www.dropbox.com/s/qwl6il4pjfih7d3/Test.pbix?dl=0

[lucadiielsi]

Not super stylish, but working! 🙂 

Thanks!

Luca