RANKX Formula Returning Duplicate Ranks

[SanketSk]

Hi There,

I have created the RANKX formula (shared below). It works perfectly when used in a table or matrix with a single column, "Spare Code." However, when I add two additional columns — "Description" and "9NC" — the formula returns incorrect and duplicate ranks. Could you please help me resolve this issue?

__ 

My Formula - 

Rank_Spare_by_Quantity =

RANKX(

    ALL(Spare_Consumption_Final_File[Part Code] ),

    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),

    ,

    DESC

)

[Ahmed-Elfeel · Accepted solution]

Hi @SanketSk,

It is hard to solve when there is no Sample data but here is some approaches you can try ☺️❤️

So First let's clarify the problem....The problem is not with your formula's logic per se but with its context transition when you add more columns

Let me Make it simpler for you to understand:

• When you only have Part Code in your visual the formula works because:

  • Each row context has only one Part Code

  • CALCULATE(SUM(Quantity)) calculates the total quantity for that specific part code
  • ALL(Part Code) ensures you're ranking against all part codes

So when you add Description and 9NC the row context now includes the combination of (Part Code + Description + 9NC) Your formula is now:

• Calculating the sum of quantity for each combination of Part Code + Description + 9NC
• But still ranking against all Part Codes (not the combinations)

What dou you need to do?

You need to modify your formula to handle the multi column context ; Here are three approaches:

• First Approach: Rank by Part Code Only (I recommend you this)
  • If you want the rank to be based solely on the Part Code total quantity (regardless of Description/9NC):

Rank_Spare_by_Quantity =
RANKX(
    ALL(Spare_Consumption_Final_File[Part Code]),
    CALCULATE(
        SUM(Spare_Consumption_Final_File[Quantity]),
        ALLEXCEPT(Spare_Consumption_Final_File, Spare_Consumption_Final_File[Part Code])
    ),
    ,
    DESC,
    DENSE
)

• Second Approach: Rank by Part Code + Description + 9NC (Combination)
  • If you want unique ranks for each combination:

Rank_Spare_by_Quantity =
VAR CurrentPartCode = MAX(Spare_Consumption_Final_File[Part Code])
VAR CurrentDescription = MAX(Spare_Consumption_Final_File[Description])
VAR Current9NC = MAX(Spare_Consumption_Final_File[9NC])
RETURN
RANKX(
    ALL(
        Spare_Consumption_Final_File[Part Code],
        Spare_Consumption_Final_File[Description],
        Spare_Consumption_Final_File[9NC]
    ),
    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),
    ,
    DESC,
    DENSE
)

• Third Approach: Using SUMMARIZE (Alternative approach) 

Rank_Spare_by_Quantity =
VAR SummaryTable =
    SUMMARIZE(
        Spare_Consumption_Final_File,
        Spare_Consumption_Final_File[Part Code],
        "TotalQty", SUM(Spare_Consumption_Final_File[Quantity])
    )
RETURN
RANKX(
    SummaryTable,
    [TotalQty],
    ,
    DESC,
    DENSE
)

Note: Try First Approach First it should give you consistent ranks where all rows for the same Part Code have the same rank value even when Description and 9NC columns are visible.

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[SanketSk]

Hi @Ahmed-Elfeel ,

The first formula is working fine for me; however, it doesn’t seem to consider the other external slicers present on the page. It appears that the formula is filtering quantities irrespective of the slicer selections. I have three different slicers on the page, please suggest if we can modify the formula to include these slicer selections so that the ranking adjusts accordingly.

Regards,
Sanket

[Ahmed-Elfeel]

Hi @SanketSk,

The issue is that ALLEXCEPT is removing all filters except for Part Code including your external slicers. We need a more precise approach that respects the external filter context.

First Approach: Use ALLSELECTED (Recommended)

• This is usually the cleanest solution:

Rank_Spare_by_Quantity =
RANKX(
    ALLSELECTED(Spare_Consumption_Final_File[Part Code]),
    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),
    ,
    DESC,
    DENSE
)

Second Approach: Use VALUES instead of ALL

• This respects the current filter context including slicers:

Rank_Spare_by_Quantity =
RANKX(
    VALUES(Spare_Consumption_Final_File[Part Code]),
    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),
    ,
    DESC,
    DENSE
)

Third Approach: More explicit approach using CALCULATETABLE

• If you need more control over which filters to preserve:

Rank_Spare_by_Quantity =
VAR CurrentPartCodes =
    CALCULATETABLE(
        VALUES(Spare_Consumption_Final_File[Part Code]),
        ALL(Spare_Consumption_Final_File[Description]),
        ALL(Spare_Consumption_Final_File[9NC])
    )
RETURN
RANKX(
    CurrentPartCodes,
    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),
    ,
    DESC,
    DENSE
)

Note: Start with First Approach (ALLSELECTED) as it iss the most straightforward and typically handles this scenario perfectly

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Zanqueta]

Hi @SanketSk ,

If I understood, your measure works correctly when only the column Part Code is present in the visual. However, when you add Description and 9NC, the ranking becomes inconsistent or duplicated?

This happens because RANKX is sensitive to the context in which it is evaluated.

When you add more columns to the visual, the row context changes — Power BI evaluates the measure for each unique combination of Part Code, Description, and 9NC. If these combinations are not unique per Part Code, the ranking becomes ambiguous or duplicated.

To ensure consistent ranking based only on Part Code, you should remove all filters from the visual except for Part Code. You can do this by using ALLSELECTED or REMOVEFILTERS depending on your needs.
Here’s a revised version using REMOVEFILTERS

Rank_Spare_by_Quantity =
RANKX(
    REMOVEFILTERS(Spare_Consumption_Final_File[Description], Spare_Consumption_Final_File[9NC]),
    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),
    ,
    DESC
)

Alternatively, if you want to rank within the current selection but ignore the extra columns

Rank_Spare_by_Quantity =
RANKX(
    ALLSELECTED(Spare_Consumption_Final_File[Part Code]),
    CALCULATE(SUM(Spare_Consumption_Final_File[Quantity])),
    ,
    DESC
)

✅ If this response resolved your issue, please mark it as correct to assist other members of the community.

[SanketSk]

Hi @GeraldGEmerick,

Thanks for quick response, I am new to Power Bi, could you please suggest suitable formula using RANK function which will work as per the requirment. Also regarding sample data, please let me know how I can share the sample data and in which format.

Regards, 

Sanket 

[GeraldGEmerick]

@SanketSk Very difficult to decipher the issue without sample data to test with. Is it possible that you could provide sample data? It is not uncommon for RANKX to return duplicate ranks, it is one of the most often cited frustrations with that function. You could instead try the RANK function.