Forum Discussion
DAX Challenge
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 |
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] ) ) )
6 Replies
When ranking, is the employment period only used for tie-breaking?
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] ) ) )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!
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] ) ) )
