How to sort matrix descending with others at the bottom.

[Anonymous]

Hi, 

I have a table which is a count the number of customer requests per day, showing top 10 customers and the rest group in "other" 

I used the following Dax code given from another guide. 

Catergory = 
VAR Summary = SUMMARIZE('Appended Data','Appended Data'[Supplier],"Count", COUNT('Appended Data'[Supplier]))
VAR _Rank = 
    MAXX(
        FILTER(
            ADDCOLUMNS(
                Summary, 
                "Rank", RANKX(Summary,[Count],Max([Count]),DESC,DENSE)
                ),
                'Appended Data'[Supplier] = EARLIER('Appended Data'[Supplier])
        ),
        [Rank]
    )
    RETURN
    IF(_Rank<=10,'Appended Data'[Supplier], "Other")

This works beautifully however, I cannot figure out to to sort my customer request descendingly with others at the bottom instead

At the moment when I sort descending:

Name	Count
Others	435
Cust 1	126
Cust 2	65
Cust 3	45
Cust 4	34
Cust 5	30
Cust 6	29
Cust 7	27
Cust 8	23
Cust 9	19
Cust 10	11

but I want this:

Name	Count
Cust 1	126
Cust 2	65
Cust 3	45
Cust 4	34
Cust 5	30
Cust 6	29
Cust 7	27
Cust 8	23
Cust 9	19
Cust 10	11
Others	435

 Appologies if this has been asked before, please link the solution.

[dax]

Hi @Anonymous , 

You could try to create an index  column like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xDsAgCIXhqzTMDiKIde4BegDjaNK52vsXJhy/P4+E1uBez3gnBGDK0EOD65vrQA2YxEPSINuA7GIzq4nd2Rzdok7VXczFfZrJXe2BbY/RAkLvPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Count", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,10000,Replacer.ReplaceValue,{"Custom"})
in
    #"Replaced Value"

 Then  set name sort by custom column in model, and set sort by name in table, you will get below result 

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi my top 10 customer table is a custom column - when I used your code and customised it to may data it only ranks 10000 and 9997  - with other rows being errors. I will try to keep testing

[dax]

Hi @Anonymous , 

You also could refer to below M code. If possible, could you please inform me your detailed information (such as your sample data(

Appended Data table's sample))? Then I will help you more correctly.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc67DcAgDATQVSLXFDHfUAJjIKZIsn98QpHd+Z2s081J472fg5kc8UnL/QHsswZegpzUQRyNozhEdYJNYRb7qi5wUV9wUFcMMP8yTgI2ARbxXtAa7r22d9zSvD4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Count", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom]<11 and [Custom] <> null  then [Name] else "others"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Name", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom.1"}, {{"sum", each List.Sum([Count]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Custom.1", "Name"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null)))),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2","","100000",Replacer.ReplaceValue,{"Custom"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

Best Regards,
Zoe Zhi

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

[Anonymous]

Sample Data 

I have to change real data into stock data in the file as its sensitive my company but otherwise the columns are all there. Hope this will not affect you trying to help me

The branch and category is there for me to use in the matrix so that it shows up with 0 instead of blanks.

They are created using the New table using Dax expression - because the category of suppliers will dynamically change.

If you need more information please let me know.