RANKX doesnt work as It should

[lucie_raboch]

Hi all,

My goal is to show top 20 master by metric Sales and the rest will be grouped like 'Others'. 

I'm working with RANKX to be able rank values, but it is showing 11111 insted of 1,2,3,4,5.

Can you tell me what I'm doing wrong please?

Result (excel version)

This is my Power BI file: https://drive.google.com/file/d/1HagYTmRAdT6SFQd1HXw7CM10jwrFFOcy/view?usp=sharing

Thank you for your help on this issue.

Lucie

[lucie_raboch]

Wow, yes, It is working now! Thank you for this solution.

[lucie_raboch]

Hi,

Ranking is working now, but I need to remove Master from the table to see only top 20 rows and Others and if I remove Master Column then Master Group is BLANK.

Also how can I sort according to the Rank?

[techies]

Hi @lucie_raboch does creating a calculated table and using its columns in a table visual work for you?

like this

TopMasterTable =

ADDCOLUMNS(

    SUMMARIZE('Sheet9', 'Sheet9'[Master]),

    "Sales", [Sales amount],

    "Sales Rank", RANKX(

        ALL('Sheet9'[Master]),

        [Sales amount],

        ,

        DESC,

        DENSE

    ),

    "MasterGroup",  

    VAR CurrentRank =

        RANKX(

            ALL('Sheet9'[Master]),

            [Sales amount],

            ,

            DESC,

            DENSE

        )

    RETURN

        IF(CurrentRank <= 20, 'Sheet9'[Master], "Others")

)

[lucie_raboch]

This is my PowerBI file: https://drive.google.com/file/d/1HagYTmRAdT6SFQd1HXw7CM10jwrFFOcy/view?usp=sharing

Can you create this table in this file and share back please?

Because it's not working properly and I don't know why.

[techies · Accepted solution]

Hi @lucie_raboch based on my understanding on the data model, here is the calculated table , please test it if it works, not able to upload pbix file

TopMasterTable =

VAR BaseTable =

    SUMMARIZE(

        'Master_Unique',

        'Master_Unique'[Master]

    )

VAR SalesWithValues =

    ADDCOLUMNS(

        BaseTable,

        "Sales",

        CALCULATE(

            SUM('FactVarMetrics'[Value]),

            'DimSce'[Scenario] = "Budget",

            'DimSce'[Metric] = "Gross",

            TREATAS(VALUES('Master_Unique'[Master]), 'DimProducts'[Master])

        )

    )

VAR WithRank =

    ADDCOLUMNS(

        SalesWithValues,

        "Sales Rank",

        RANKX(

            SalesWithValues,

            [Sales],

            ,

            DESC,

            DENSE

        )

    )

RETURN

    ADDCOLUMNS(

        WithRank,

        "MasterGroup",

        IF([Sales Rank] <= 20, [Master], "Others")

    )

[techies]

Hi @lucie_raboch please check this measure for grouping

Master Group =

VAR CurrentRank =

    RANKX(

        ALL('Sheet9'[Master]),

        [Sales amount],

        , DESC,

        DENSE

    )

RETURN

IF(CurrentRank <= 20, SELECTEDVALUE('Sheet9'[Master]), "Others")

[techies]

Hi @lucie_raboch for ranking you can use this 

Rank by Sales =

RANKX(

    ALL('Sheet9'[Master]),

    [Sales amount],

    , DESC,

    DENSE

)

where [sales amount] is a measure as this-- 

Sales amount = SUM('Sheet9'[Sales])

[lucie_raboch]

Thank you for your answer, but your solution doesnt work, still getting 1,1,1,1,1

[DataNinja777]

Hi @lucie_raboch ,

To create a proper ranking for your "Master" by Sales in Power BI and group everything outside the Top 20 as "Others", you need to use RANKX in a way that evaluates all values in the dataset regardless of what’s currently being filtered by the visual. The reason you were getting only 1s is likely because the RANKX function was evaluating over a single visible row context due to the table visual.

You can fix it by first calculating the ranking using the ALL function inside RANKX to remove any filters on the "Master" column. Here's the corrected measure:

Ranking = 
RANKX(
    ALL('YourTable'[Master]),
    CALCULATE(SUM('YourTable'[Sales])),
    ,
    DESC,
    DENSE
)

To create a grouping for Top 20 and "Others", define a new column or measure like this:

Master Group = 
VAR CurrentRank = 
    RANKX(
        ALL('YourTable'[Master]),
        CALCULATE(SUM('YourTable'[Sales])),
        ,
        DESC,
        DENSE
    )
RETURN 
    IF(CurrentRank <= 20, 
        SELECTEDVALUE('YourTable'[Master]), 
        "Others"
    )

Then you can use Master Group as the row field in your matrix and sum of Sales as the value. This will show the Top 20 masters individually and group everything else under "Others".

If you want to make sure “Others” always shows up at the bottom, create a sort order measure like this:

Sort Order = 
VAR CurrentRank = 
    RANKX(
        ALL('YourTable'[Master]),
        CALCULATE(SUM('YourTable'[Sales])),
        ,
        DESC,
        DENSE
    )
RETURN 
    IF(CurrentRank <= 20, CurrentRank, 999)

Use this to sort the "Master Group" column. This ensures that "Others" comes last in your visual.

Best regards,