Sorting by Specific Columns in Matrix

[Anonymous]

Hi,

If I remember correctly, Excel let's me sort a pivot table using each individual column. However, I haven't been able to figure out how to do that with Power BI. It only let's me sort by the total column. Has anyone had a different experience or know a workaround?

[ggyczew]

It's  August 2023 and Microsoft still did not do anything.

[v-jingzhang]

Hi @Anonymous 

Unfortunately sorting by column is not supported in Matrix visual currently. There are some similar ideas about this requirement. You can vote them up:

Microsoft Idea - matrix can be sorted by any column by clicking on the column header

Microsoft Idea - Sort in Matrix Visual, just like any column can be sorted in the visual in a Table.

Microsoft Idea - Sort any column header by clicking on it in Matrix Table

Hope this feature will be available some day. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[TheoC]

Hi @Anonymous 

A Pivot table in Excel is kind of the Matrix table in Power BI.  If you switch from a Table visual to a Matrix visual, you will be able to sort by various columns, directions. 

The Matrix visual is highlighted in the below screenshot:

Hope this helps!

Theo 🙂

[Anonymous]

In Excel I can sort the data by any column. However, in the matrix, I can only seem to sort by the Total column.

I would like the user to be able to sort by any month. For instance, they could sort by February totals which would make Ned come to the top since he had the most that month. 

Any ideas of how to do this?

[TheoC]

Hi @Anonymous 

Apologies, I misunderstood your request. The sorting is limited to that which you can do via the Sort By option when selecting the menu. Workarounds exist but are very limited and don't give the ability to sort by pressing the column header.

Although not the exact solution you want, one way to give the user the ability to sort by specific columns is have a Month / Year slicer, and sort the months out by Rank.  Here is an example of what I am referring to: https://community.powerbi.com/t5/Desktop/Top-N-Ranking-for-specific-year/m-p/2122020#M785126

Theo

[TheoC · Accepted solution]

@Anonymous okay, some positive news.  You can sort by columns, however, you need to create measures for each specific column that you want in your Matrix.  So, for example, in this instance, you have months Jan 2022, Feb 2022, March 2022 and April 2022.  To achieve the sorting, you would need to create measures for each one.  It's not ideal but it's one way of achieving a workaround:

Jan 2022 = CALCULATE ( SUM ( 'Table'[Amount] ) , FILTER ( 'Date' , 'Date'[Mmm YYYY] = "Jan 2022" ) )

Feb 2022 = CALCULATE ( SUM ( 'Table'[Amount] ) , FILTER ( 'Date' , 'Date'[Mmm YYYY] = "Feb 2022" ) )

Mar 2022 = CALCULATE ( SUM ( 'Table'[Amount] ) , FILTER ( 'Date' , 'Date'[Mmm YYYY] = "Mar 2022" ) )

Apr 2022 = CALCULATE ( SUM ( 'Table'[Amount] ) , FILTER ( 'Date' , 'Date'[Mmm YYYY] = "Apr 2022" ) )

Just change the [Mmm YYYY] column to whatever identifies the specific month in your Date table and similarly change the "Jan 2022" etc to whatever the specific naming convention is that you have in your Date table as well.

Again, not the ideal solution but gives you what you are after. Just add the measures as values in your Matrix table and do not add anything in the "columns".

All the best.

Theo

[Anonymous]

Thanks Theo. I colleague proposed this solution. Would be a little annoying to have to add the new column every month, but at good to know that at least there is an option.

[TheoC]

@Anonymous hopefully it will be implemented soon enough as a simple function of the Matrix table! Best of luck with it mate.