Top5 & Others -- Sorting Problem

[Anonymous]

Hi All,

i really need help on this one. I copied the measure to get Top5 & Others (i copied, it is too advance for my PBI knowledge). It works perfectly but i have a problem with the sorting. As you can see in the picture, everything is sorted by SALES (Measure) but i would need to keep "ZZ_Other" Category always at the bottom. I am using the formula below to get the Top5 & Others Measure. Where i can adjust it the keep "Others" always at the bottom while keeping everything else sorted by sales.

Thanks

Luca

APIwOthers Sales PY =
VAR Items =
    SELECTCOLUMNS ( ALL ( NewAPI ), "RankItem", NewAPI[API Description] )
VAR ItemsWithValue =
    ADDCOLUMNS (
        Items,
        "RankMeasure", CALCULATE ( [Sales], ALL ( XXX_API[API Description] ) )
    )
VAR ItemsWithRank =
    ADDCOLUMNS (
        ItemsWithValue,
        "Rank", RANKX ( ItemsWithValue, [Sales], [Sales], DESC, DENSE )
    )
VAR ItemsWithTop =
    ADDCOLUMNS (
        ItemsWithRank,
        "TopOrOthers", IF ( [Rank] <= 5, [RankItem], "ZZ_Other APIs" )
    )
VAR ItemsFinal =
    SELECTCOLUMNS (
        FILTER (
            ItemsWithTop,
            CONTAINSROW ( VALUES ( NewAPI[API Description] ), [TopOrOthers] )
                && CONTAINSROW ( VALUES ( XXX_API[API Description] ), [RankItem] )
        ),
        "TopN_Others", [RankItem]
    )
RETURN
    CALCULATE ( [Sales PY], TREATAS ( ItemsFinal, NewAPI[API Description] ) )

Actual Result

[Anonymous · Accepted solution]

Hi @Anonymous,

You can create a custom sort table with all category values and index who used to setting 'sort by column' property. Then you can use category link to raw table and use new table category fields on the matrix to achieve custom sorting.

Custom Sorting in Power BI 
Regards,

Xiaoxin Sheng

[ChrisMendoza]

@Anonymous - Have you taken a look at → https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/