Forum Discussion

Tlotly's avatar
Tlotly
Icon for Helper V rankHelper V
1 year ago
Solved

Breaking a tie issue

Good day,
 
I need help with the ranking measure below. It's not able to break the tie when two branches have a tie on Variance % and the underlying values (sumGroups/sumBudget) for Variance %  are the same.
What I really need the measure to do is the following:
  • If there is a tie on Variance % and sumGroup is different for those branches, use highest sumGroup to break the tie.
  • If there is a tie on Variance % and sumGroup is the same for those branche, use highest Variance % and sumPO to break the tie.
budgetRank =
IF(
    ISBLANK([Variance %]),
    BLANK(), -- Show blank for rows with blank measure results
    RANKX(
        FILTER(
            ALLSELECTED('Centre details'[Branch Description]),
            NOT(ISBLANK([Variance %])) -- Only rank non-blank rows
        ),
        [Variance %],
        ,
        DESC, -- Rank in descending order
        DENSE
    ) +
    IF(
        COUNTROWS(
            FILTER(
                ALLSELECTED('Centre details'[Branch Description]),
                [Variance %] = [Variance %] &&
                [sumGroups] / [sumBudget] <> [sumGroups] / [sumBudget]
            )
        ) > 0,
        RANKX(
            FILTER(
                ALLSELECTED('Centre details'[Branch Description]),
                NOT(ISBLANK([Variance %])) -- Only rank non-blank rows
            ),
            [sumGroups],
            ,
            DESC, -- Rank in descending order
            DENSE
        ) * 0.0001, -- Small weight to break ties
        RANKX(
            FILTER(
                ALLSELECTED('Centre details'[Branch Description]),
                NOT(ISBLANK([Variance %])) -- Only rank non-blank rows
            ),
            [Variance %] + [sumGroups] + [sumPO],
            ,
            DESC, -- Rank in descending order
            DENSE
        ) * 0.000001 -- Smaller weight to break further ties
    )
)
 
Refer to the matrix below how the results look like. I need Madibogo Branch to be ranked 31.

Thank you in advance

 

Need Help
Tips and Tricks
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    To calculate Rank you can try:

    RankBranch =
    RANKX(
    ALL('YourTable'),
    CALCULATE(
    [Variance%] + 
    DIVIDE([sumGroups], 1000000) + 
    DIVIDE([sumPO], 1000000000000) 
    ),
    ,
    DESC,
    DENSE
    )

    This should break ties in the desired manner. Let me know if it works for your scenario!

     

    If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

4 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Icon for Solution Sage rankSolution Sage
    1 year ago

    Hi!

    So it looks like you have the right idea in your measure. You use Variance % as a base and then want to use small weights to break ties. The issue, I think, is that you first calculate the RANKX() of only Variance %, and only then add conditions with small weights. But by then, the rank is already decided.

     

    You have a long DAX statement there so I might have missed something here, but I hope you see the principle of what I think you can do below. Add all the factors in the first RANKX(). If only Variance % is needed it doesn't matter that the small weights are there, but if you need them they are applied instantly. As a bonus, it would make the entire statement a lot shorter, too.

     

    Try it out and let me know how it went! 

     

    budgetRank =
IF(
    ISBLANK([Variance %]),
    BLANK(), -- Show blank for rows with blank measure results
    RANKX(
        FILTER(
            ALLSELECTED('Centre details'[Branch Description]),
            NOT(ISBLANK([Variance %])) -- Only rank non-blank rows
        ),
        [Variance %] + -- Main rank decider
        [sumGroups] * 0.0001 + -- Second rank decider
        [sumPO] * 0.000001, -- Third rank decider
        ,
        DESC, -- Rank in descending order
        DENSE
    )
)

     

     

    • Tlotly's avatar
      Tlotly
      Icon for Helper V rankHelper V
      1 year ago

      Thank you very much TomasAndersson 

       

      It works! I only had to add * 1000  to the Variance % measure as the measure seemed to not read decimals correctly.

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    To calculate Rank you can try:

    RankBranch =
    RANKX(
    ALL('YourTable'),
    CALCULATE(
    [Variance%] + 
    DIVIDE([sumGroups], 1000000) + 
    DIVIDE([sumPO], 1000000000000) 
    ),
    ,
    DESC,
    DENSE
    )

    This should break ties in the desired manner. Let me know if it works for your scenario!

     

    If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

    • Tlotly's avatar
      Tlotly
      Icon for Helper V rankHelper V
      1 year ago

      Thank you very much Kedar_Pande 

       

      It works! I only had to add * 1000  to the Variance % measure as the measure seemed to not read decimals correctly.