Sorting TOP N plus OTHERS

Hello Ania26 , 

Please checkout this video this will guide you to create TopN and Others in Visual.

https://www.youtube.com/watch?v=yGFcCbXn_g0&pp=ygULVG9wbiBnb29kbHk%3D

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

Thanks for the reply from dharmendars007 , please allow me to provide another insight:

Hi, Ania26 
 

Could you please let us know if dharmendars007's response resolved your issue? If it did, kindly accept it as the solution.

Of course, if you have your own solution, feel free to share it. This will help other community members with similar issues find a resolution more quickly.
 

Firstly, your idea is excellent; however, as far as I understand, it is currently not feasible to implement. We have two methods for sorting the legend:

Sort.legend, which is clearly not a viable option.

Sort by column, but this has a significant limitation:

if there are duplicate values in the column being sorted, the values in the sorting column must also be the same. Therefore, if your requirement is for the legend to display the top five ORIGIN values based on value, with the first being “Other,” this cannot be fully achieved at present. For instance, if a particular region ranks third in 2023, it will also rank third in 2024, as it cannot dynamically adjust based on the current year’s value to show the top N.

As it stands, we can achieve the following: the first entry will be “Other,” and the remaining ORIGIN values will be sorted to display the top five based on value.
1.Below is my reference example:

2.Firstly, you will need to process the data using the following M language:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUXJKzUnPLM0FssyUYnXgwu5FqanJqUCGObJoQH5OYl4KkGGJLJqUBCSMkUUSE4GECbKIX2pJRmoRSHcxkGcBkzJBcQGKMKYLTJBdYIIsimq6GbJUcE5+WWJ2JshBpii2luakJxaBxY2RxVGdbgLznqFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Origin = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(
    Source, 
    {"Year"}, 
    {
        {"Count", each Table.AddIndexColumn(Table.Sort(_, {{"Value", Order.Ascending}}), "index", 1, 1)}
    }
),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Origin", "Value", "index"}, {"Count.Origin", "Count.Value", "Count.index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "group", each if [Count.index] <= 5 then [Count.Origin] else "OTHERS"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{ {"group", type text}, {"Count.index", Int64.Type}, {"Count.Value", Int64.Type}, {"Count.Origin", type text}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type3", {"group"}, {{"Count", each _, type table [Year=nullable text, Count.Origin=nullable text, Count.Value=nullable number, Count.index=nullable number, group=nullable text]},{"Count2", each List.Sum([Count.Value]), type nullable number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each if [group]= "OTHERS" then 1 else 0),
    index1=Table.AddRankColumn(#"Added Custom1","index1",{{"Custom",Order.Ascending},{"group",Order.Ascending},{"Count2",Order.Ascending}}),
    #"Expanded Count1" = Table.ExpandTableColumn(index1, "Count", {"Year", "Count.Value"}, {"Count.Year", "Count.Count.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Count1",{"Custom", "Count2"})
in
    #"Removed Columns"

3.The result of this step is as follows:

4.Secondly, in Desktop, sort based on the generated index column:

5.The final result is as follows:

Please find the attached pbix relevant to the case.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.