Forum Discussion

SanketSk's avatar
SanketSk
Frequent Visitor
8 months ago
Solved

RANKX Formula Returning Duplicate Ranks

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 — "Descrip...
Need Help
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    8 months ago

    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.
Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
8 months ago

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's avatar
SanketSk
Frequent Visitor
8 months ago

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's avatar
    Ahmed-Elfeel
    Super User
    8 months ago

    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.