Forum Discussion
Custom sort in matrix table
The way I would be doing it is to get it into the same table, either by merging or if your list is fixed you could simply write a quick SWITCH statement to create a new column on your main table.
Order by = SWITCH( 'table'[variable],
"Sum of Total HLS", 1,
"Sum of List_Price", 2,
"Sum of Excise", 3......)
then use the 'sort by' to simply influence the order.
I think it is but I may be a little slow on the uptake, so I currently have the attribute set up in a related sorting table with a ranking with the hope that once I went through the steps I outlined I would be able to custom sort the attributes but It didnt work, would you mind going into more detail on the steps you mentioned. Thanks in advance
Hi Oconnd02 ,
If you need to rank the value field for every Attribute categories, then try to create column in DAX below.
Column1 =RANKX(FILTER(Table1,Table1[Attribute]=EARLIER(Table1[Attribute])),Table1[value],,ASC,Dense)
You can go through the link https://docs.microsoft.com/zh-cn/dax/rankx-function-dax to learn more about the RANKX .
Or if you need to custom sort column1 by column2, you can select the column1 in Field Pane first, then click the Sort by Column button.
If I misunderstood it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Amy, tried the below but didnt get the desired outcome, the below is what my table is currently looking like (so current attributes sorted by a-z), the below is the desired sorting of the attributes based on P&L flow, as I said I have tried the new table sorting to no avail, do you know what I am doing wrong?
Thanks
Dave
Current ViewDesired View
The way I would be doing it is to get it into the same table, either by merging or if your list is fixed you could simply write a quick SWITCH statement to create a new column on your main table.
Order by = SWITCH( 'table'[variable],
"Sum of Total HLS", 1,
"Sum of List_Price", 2,
"Sum of Excise", 3......)
then use the 'sort by' to simply influence the order.The Matrix visual lacks functionality. There are lots of ideas on the community site. Below are the front runners. Please vote these up!
