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

DAX DAX Calculations
1

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

2

@Hemantsingh,

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

Hope this is helpful.

  • Brian
2 Likes
3

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

4

@Hemantsingh,

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
5

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

image
image545Ă—569 13.7 KB

5 Likes
6

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