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

Question

Hi Guys,&nbsp;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).&nbsp;In this case, I want to display the TOP 5 sales rep for&nbsp;a given month by the total sales count and break ties by the total value of the contracts.&nbsp;The issues I'm having is that RANX&nbsp;doesn;t handle the ties the need I want (unique IDs)&nbsp;this is what i get with RANKX:DATE TOTAL SALES &nbsp; &nbsp; TOTAL ACV &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;RANK &nbsp; &nbsp;RANK(SKIP) &nbsp; RANK NEEDED:2017-01-01 &nbsp; &nbsp;10 &nbsp; &nbsp; &nbsp; &nbsp;10,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12017-01-01 &nbsp; &nbsp; 8 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22017-01-01 &nbsp; &nbsp; 6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32017-01-01 &nbsp; &nbsp; 6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 42017-01-01 &nbsp; &nbsp; 6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 52017-01-01 &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 62017-01-01 &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 72017-01-01 &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 82017-02-01 &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12017-02-01 &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22017-02-01 &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32017-03-01 &nbsp; &nbsp; 6 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12017-03-01 &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4,000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22017-03-01 &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3,900 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3.....&nbsp;thids is the formula i'm currently using:&nbsp;Rank_NewDealsAccounting = RANKX(FILTER(MONTHLY_ACTIVITY,MONTHLY_ACTIVITY[DATE]=EARLIER(MONTHLY_ACTIVITY[DATE])),MONTHLY_ACTIVITY[TOTAL WINS ACCOUNTING] ,,,Skip)&nbsp;any help would be really appreciated.&nbsp;thank you!&nbsp;ps. i was able to get what i wanted on MYSQL but i wasnt able to use variable in the power bi query editory

v-ljerr-msft · Accepted Answer

Hi&nbsp;NormnSG,
&nbsp;
A&nbsp;pattern can be&nbsp;used in this situation is:
&nbsp;
Final value to be ranked&nbsp;=
Rank on Primary Measure (ascending)
+ Rank on Secondary Measure (ascending) / (Total Row Count + 1)
&nbsp;
So the formula below should work in your scenario.&nbsp;:smileyhappy:
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
            )
        )
)

&nbsp;
Regards

Forum Discussion

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

2 Replies

Related content

RankX at Category level for Category/SubCategory context

RankX

RankX - Unique ranking for Duplicate Values

Using RANKX within a category

Ranking Column by Sub Category (RANKX)