# Ranking (Breaking Ties) for a Category based on a Measure with duplicate values for categories

Hi Guys,

Here is the scenario . I have a category column and a measure which is simply the count of rows for the table. Now i need to rank the categories of that table based on the count measure. Thing is there are a lot of categories for which the count is tied or same.

First i need to break the ties so that rank can be given in ordinal/continuous fashion like 1,2,3,4,5 without any repetition of rank.

Solution which I have tried are also in the pbix. For heads up i have tried break the ties using median and also tried to break the ties with rand and randbetween.Rank With Ties issue.pbix (66.4 KB)

Attaching the sample PBIX for everyone.

@Greg @quantumudit @Harsh @AntrikshSharma. Tagging you guys as i need it a little urgently.

best regards,
Hemant

I actually did a video on this exact topic earlier this summer:

Hope this is helpful.

• Brian
2 Likes

Hi @Hemantsingh. Took a quick look … I can’t see a way forward without additional info. As per @BrianJ’s post, I think you’ll need to have rules defined for tiebreaking (i.e., multiple rankings); RAND() in my experience has never been accepted by the client as a “rule”. Good luck. Greg

Even if RAND were an acceptable tiebreaker, be really careful – RAND behaves very oddly in DAX and PQ, and my experience is that you throw that into the mix with RANKX, it can generate some really weird results.

• Brian

@Hemantsingh Try this:

Alphabetical Rank =
IF (
ISINSCOPE ( 'Sheet 1'[Step] ),
VAR CurrentStep =
SELECTEDVALUE ( 'Sheet 1'[Step] )
VAR Result =
COUNTROWS (
FILTER (
ALL ( 'Sheet 1'[Step] ),
'Sheet 1'[Step] >= CurrentStep
)
)
RETURN
Result
)

.

Ranking =
IF (
ISINSCOPE ( 'Sheet 1'[Step] ),
RANKX (
ALL ( 'Sheet 1'[Step] ),
[Total Reject] + DIVIDE ( [Alphabetical Rank], 100 ),
,
DESC,
SKIP
)
)

5 Likes

Hey @AntrikshSharma,

Once again you have come up with a solution that has its roots in excel, simple and easy. I didn’t know that alphabetical ranking is also possible. Your excel roots are really paying off.

Thanks for helping out.

@BrianJ @Greg Thanks for putting in the effort on request and responding at earliest.

Best Regards,
Hemant

2 Likes