Custom sort in matrix table

[Oconnd02]

HI All 

I am looking for help in custom sorting in the matrix table in order to set up a BI P&L report. Due to the nature of my core data I had to unpiviot columns in order to get long clean data and have an attributes and value column which I am using as the core columns for my P&L. 

In order to sort this data I have set up a new sort table based on the attributes column (using 1 to 20) and set up a relationship with the core data. I have then set up a new column with the related table column formula and then tried to custom sort on this new column. However this does not pull through any ranking numbers (my 1 to 20 above) and then hence I cannot sort the attribute data based on my 1 to 20 ranking. I must be doing something wrong but cannot see it based on what I have found on this forum, any help out there? 

Cheers

[Anonymous]

The way I would do this is to have the attribute you're trying to sort by in the same table - then you can simple use the 'sort by column' to sort your main data by the attribute.

I can go into more detail if this is of any value.

[Oconnd02]

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

[v-xicai]

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.

[Oconnd02]

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

[Anonymous · Accepted solution]

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.

[garythomann]

The Matrix visual lacks functionality.  There are lots of ideas on the community site.  Below are the front runners.  Please vote these up!

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11185431-add-a-multiple-columns-s...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...