Sorting a matrix second level - sort by column not working

[Captenaj]

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 second level may be months in which they are sold. I have created a new table with the order that I want the second level sorted on (January =1, Feb = 2, etc), linked this to the table that has the months the item sold and used sort by column. I cannot get it to sort the second level as desired. Some suggestions are to add the new MonthOrder column to tooltips but that's not available in a matrix. Any help is appreciated.

[v-cherch-msft]

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]

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]

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]

v-cherch-msft, thank you very much for your time. It may be sinking in finally.

Yes, I want to sort as you specify here: first by one parameter descending, then sort each of those groupings by a second parameter (which is a custom order) ascending. The questions/problems I am having are:

- Am I correct that if this is what I want to do, your Table3 isn't needed here?

- In the table that contains the data, I do not have a "MonthNo" column so I created it with an IF/THEN statement.

- The measure "Total Numbers" appears to add all the values in the Numbers column. What I want to do is sort the first column by number of rows, not by the value. How could I do that? 

Thank you to all who have responed.

[v-cherch-msft]

Hi @Captenaj 

---

- Am I correct that if this is what I want to do, your Table3 isn't needed here?

- In the table that contains the data, I do not have a "MonthNo" column so I created it with an IF/THEN statement.

---

Yes,correct.

There is no way to sort the first column by one parameter and the second column by a different parameter.To achieve your requirement,you may create a measure to get the sort and then sort by measure.

---

- The measure "Total Numbers" appears to add all the values in the Numbers column. What I want to do is sort the first column by number of rows, not by the value. How could I do that? 

---

Change the total measure like below.Then you may get the sort.Please refer to attached file.

TotalRows = CALCULATE(COUNT(Table3[Items]),ALLEXCEPT(Table3,Table3[Items]))

Regards,

Cherie

[Captenaj]

Thank you. I am seeing the matrix sorted by Measure only. The first column is also sorted by measure so the order is B, A, C, not B, C, A as required. I think what your measure is doing is trying to calculate a value based on the Number and the MonthNo so we can sort only by that one value. Is there no way to sort subcategories? I have at least a thousand categories that I am going to be filtering but the "months" remains consistent. Can't I just somehow sort by MonthNo? Is there a better way to present the data than a matrix? Thank you again.

[v-cherch-msft]

Hi @Captenaj 

Not fully understand it.How about sorting rows in query editor like below?

Regards,

[v-cherch-msft]

Hi @Captenaj 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

[Captenaj]

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.

[v-cherch-msft]

Hi @Captenaj 

Please check the attached file Sorting a matrix second level_2.pbix in above reply.It shows you how to get the sort only for the second level.

Regards,

[Captenaj]

Thank you for the reply. So are you saying there is no way to sort the first column by one parameter and the second column by a different parameter?