RANKX Formula Returning Duplicate Ranks

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.