topic DAX Challenge in DAX Commands and Tips

DAX Challenge

Anonymous — Mon, 22 Mar 2021 19:43:25 GMT

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

Re: DAX Challenge

AlexisOlson — Mon, 22 Mar 2021 21:05:04 GMT

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

Re: DAX Challenge

Anonymous — Mon, 22 Mar 2021 21:06:41 GMT

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

Re: DAX Challenge

CNENFRNL — Tue, 23 Mar 2021 10:43:31 GMT

Anonymous , 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] ) ) )

Re: DAX Challenge

Anonymous — Tue, 23 Mar 2021 01:56:37 GMT

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!

Re: DAX Challenge

CNENFRNL — Tue, 23 Mar 2021 10:47:24 GMT

Anonymous , 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] ) ) )

Re: DAX Challenge

Anonymous — Wed, 24 Mar 2021 18:13:37 GMT

Thanks!