Forum Discussion
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 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.
11 Replies
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
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.
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
