Forum Discussion

Oconnd02's avatar
Oconnd02
Regular Visitor
7 years ago
Solved

Custom sort in matrix table

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Oconnd02
      Regular Visitor
      7 years ago

      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's avatar
        v-xicai
        Community Support
        7 years ago

        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.