Forum Discussion
Sorting by Specific Columns in Matrix
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
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 🙂
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?
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
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
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.