Forum Discussion
Breaking a tie issue
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
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.