DAX RANKX - how can I get unique rank by categories?

[NormnSG]

Hi Guys,

I'm trying to show the TOP 5 only (no ties and not multiple values for a rank i.e. 1,2,2,2,3,4,5).

In this case, I want to display the TOP 5 sales rep for a given month by the total sales count and break ties by the total value of the contracts.

The issues I'm having is that RANX doesn;t handle the ties the need I want (unique IDs)

this is what i get with RANKX:

DATE TOTAL SALES     TOTAL ACV           RANK    RANK(SKIP)   RANK NEEDED:
2017-01-01    10        10,000                   1             1                       1
2017-01-01     8           6,000                   2             2                       2
2017-01-01     6           9,000                   3             3                       3

2017-01-01     6           8,000                   3             3                       4

2017-01-01     6           7,000                   3             3                       5

2017-01-01     4           5,000                   4             6                       6

2017-01-01     4           4,000                   4             6                       7

2017-01-01     2           9,000                   3             8                       8

2017-02-01     4           5,000                   1             1                       1

2017-02-01     4           4,000                   1             1                       2

2017-02-01     2           9,000                   2             3                       3

2017-03-01     6           5,000                   1             1                       1

2017-03-01     5           4,000                   2             2                       2

2017-03-01     5           3,900                   2             2                       3

.....

thids is the formula i'm currently using:

Rank_NewDealsAccounting = RANKX(FILTER(MONTHLY_ACTIVITY,MONTHLY_ACTIVITY[DATE]=EARLIER(MONTHLY_ACTIVITY[DATE])),MONTHLY_ACTIVITY[TOTAL WINS ACCOUNTING] ,,,Skip)

any help would be really appreciated.

thank you!

ps. i was able to get what i wanted on MYSQL but i wasnt able to use variable in the power bi query editory

[eneri]

This is an insane calculation, why can't Power BI have an option "UNIQUE" alongside "DENSE" and "SKIP"? 

[v-ljerr-msft · Accepted solution]

Hi @NormnSG,

A pattern can be used in this situation is:

Final value to be ranked =

Rank on Primary Measure (ascending)

+ Rank on Secondary Measure (ascending) / (Total Row Count + 1)

So the formula below should work in your scenario. 

Final Rank = 
RANKX (
    FILTER (
        MONTHLY_ACTIVITY,
        MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] )
    ),
    RANKX (
        FILTER (
            MONTHLY_ACTIVITY,
            MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] )
        ),
        MONTHLY_ACTIVITY[TOTAL SALES],
        ,
        ASC
    )
        + DIVIDE (
            RANKX (
                FILTER (
                    MONTHLY_ACTIVITY,
                    MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] )
                ),
                MONTHLY_ACTIVITY[TOTAL ACV],
                ,
                ASC
            ),
            (
                COUNTROWS (
                    FILTER (
                        MONTHLY_ACTIVITY,
                        MONTHLY_ACTIVITY[DATE] = EARLIER ( MONTHLY_ACTIVITY[DATE] )
                    )
                )
                    + 1
            )
        )
)

Regards