Forum Discussion

Captenaj's avatar
Captenaj
Frequent Visitor
7 years ago

Sorting a matrix second level - sort by column not working

In a matrix I am trying to sort the top level the hierarchy by most common but the second level by another factor. For instance, the top level may be items sold sorted by number of items, and the sec...
Need Help
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Captenaj 

 

You may refer to this post and create a measure to get the rank.Then sort by the measure.For example:

Sort by total Numbers (DESC)-->Sort by Month (ASC)

Measure =
VAR e =
    SELECTEDVALUE ( Table3[MonthNo] )
VAR c = [TotalNumbers]
VAR t =
    SUMMARIZE ( ALLSELECTED ( Table3 ), Table3[Items], Table3[MonthNo] )
RETURN
    COUNTROWS (
        FILTER ( t, ISONORAFTER ( [TotalNumbers], c, ASC, Table3[MonthNo], e, DESC ) )
    )

Regards,

Cherie

  • Captenaj's avatar
    Captenaj
    Frequent Visitor
    7 years ago

    I see what Table3[MonthNo] and Table3[Items] represent but what column does [TotalNumbers] represent? Why isn't Sort By Column working when multiple other posts recommend that as the solution? 

     

    Thank you very much for your time.

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi Captenaj 

       

      If you want to sort two columns in matrix.Sort by column cannot work well as it can only be used to sort by one column.

      In my example,the first level (Item)-->Sort by total Numbers (DESC):120>60>30

      Then the second level (Month)-->Sort by Month (ASC):Jan<Feb<Mar

       

      If you just want to sort the second level.You may create a month table and use 'sort by column' for it.Attached file for your reference.

      Regards,

      Cherie

      • Captenaj's avatar
        Captenaj
        Frequent Visitor
        7 years ago

        Thank you for your reply. In the matrix I want to sort the first column by numbers (most common at the top). In your example the order would be B, C, A. The next column I want to sort by month order: Jan, Feb, March. In your data the first column is sorted alphabetically and the second by month. Just as you said "If you just want to sort the second level.You may create a month table and use 'sort by column' for it." That's exactly what I want to do, I just want to sort the second level and I want to leave the top level alone. How can I do this?

         

        Thank you for your help.