Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Tlotly
Resolver II
Resolver II

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.
Tlotly_0-1728988694741.png

Thank you in advance

 

1 ACCEPTED SOLUTION
Kedar_Pande
Memorable Member
Memorable Member

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

View solution in original post

4 REPLIES 4
Kedar_Pande
Memorable Member
Memorable Member

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

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.

 

TomasAndersson
Solution Sage
Solution Sage

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
    )
)

 

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.