DAX Challenge

[ranjitsingh]

Hi All,

I have the following sales table where I need to find annual ranking of salesperson but there is a small complexity....

The ranking depends not only on number of annual car sales by the sales person but also takes into account how long the employee has been with the company. Shorter employment period is ranked higher. E.g. Sale of 70 cars for sonita will be ranked higher than sale of 70 cars by ravita as sonita has been with the company since only 2019, and still sold the same # of cars as ravita who has much greater experience..

Not sure how to write this in DAX to get a ranking system...?? 

EMPLOYEE_ID	FIRST_NAME	Annual Car Sales	Date of Joining	Annual Ranking
100	vinita	110	1-Jan-05
110	sonita	70	12-Jun-19
120	ravita	70	19-Apr-04
130	sarita	50	1-Mar-08
140	minita	95	12-Feb-20
150	binita	30	2-Oct-14
160	lonita	45	16-Sep-11
170	robita	65	11-Oct-09
180	papita	27	4-May-18
190	tomita	20	23-Dec-20

[CNENFRNL]

@ranjitsingh , you might want to try

Annual Ranking = 
RANKX (
    ALLSELECTED ( Sales[EMPLOYEE_ID] ),
    CALCULATE (
        SUM ( Sales[Annual Car Sales] ) + SUM ( Sales[Date of Joining] ) / 10000, 
        ALLEXCEPT(Sales, Sales[EMPLOYEE_ID] )
    )
)

[ranjitsingh]

@CNENFRNL  I dont have my workstation currently, but will try this in office tomorrow and let you know if this works. Thanks so much for your help!

[CNENFRNL · Accepted solution]

@ranjitsingh , I chanced on this video and improved the measure; you might want to refer to it as well,

Annual Ranking =
VAR __delta = MAX ( Sales[Date of Joining] )
RETURN
    RANKX (
        ALLSELECTED ( Sales[EMPLOYEE_ID] ),
        CALCULATE (
            SUM ( Sales[Annual Car Sales] ) * __delta + SUM ( Sales[Date of Joining] ),
            ALLEXCEPT ( Sales, Sales[EMPLOYEE_ID] )
        )
    )

[ranjitsingh]

Thanks!

[AlexisOlson]

When ranking, is the employment period only used for tie-breaking?

[ranjitsingh]

yes indeed only for tie breaking..sorry should have been more clear ...