Forum Discussion
Helper IIDAX to define Overall rank based on 2different Ranks
Hello All,
I need to define rank for each employee based on average of 2 defined ranks.
Below is the original data:
| Employee | TaskRank | SLA | Avg(Task+SLA) |
| John | 2 | 3 | 2.5 |
| claire | 4 | 2 | 3 |
| Chris | 4 | 4 | 4 |
| Rock | 2 | 6 | 4 |
| Bella | 6 | 3 | 4.5 |
| Harry | 7 | 1 | 4 |
| Stefanie | 1 | 5 | 3 |
The result overall rank shall be based on Avg values:
| Employee | TaskRank | SLA | Avg(Task+SLA) | Overall Rank |
| John | 2 | 3 | 2.5 | 1 |
| claire | 4 | 2 | 3 | 2 |
| Chris | 4 | 4 | 4 | 3 |
| Rock | 2 | 6 | 4 | 3 |
| Bella | 6 | 3 | 4.5 | 4 |
| Harry | 7 | 1 | 4 | 3 |
| Stefanie | 1 | 5 | 3 | 2 |
Kindly help. Thanks in advance.
9 Replies
siziAvg column is a measure. The above dax is giving error because of that.
Hi sizi ,
If you want to use measure, try it:
Measure = var _t = ADDCOLUMNS('Table',"Rank",RANKX(ALL('Table'),'Table'[Avg(Task+SLA)],,ASC,Dense)) RETURN MAXX(_t,[Rank])Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi sizi ,
Try the following expression:
Rank = var _t = ADDCOLUMNS('Table',"Rank",RANKX(ALL('Table'),[AVG],,ASC,Dense)) RETURN MAXX(_t,[Rank])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- sizi
It isnt working for me. Task rank, sla rank is all measure defined from measures. I dont know the dax doesnt work for me 😞
- sizi
Below is the data detaile data and how the calculation is done. Task rank is defined based on total task ; Avg sla is sla complaint task against task qulaified for SLA out of Total task and the result is defined rank and then task rank + sla rank gives the overall avg which again is defined to get the final rank.
Employee Total Task TaskRank Task Qualified for SLA SLA Complaint Task out of Qualified Avg of sla complaint Avg SLA rank Total Rank Avg (TaskRank+AvgSLARank Final Rank John 27 1 7 3 42.85714286 3 2 2 claire 16 4 5 2 40 4 4 4 Chris 22 2 5 4 80 1 1.5 1 Rock 14 5 6 3 50 2 3.5 5 Bella 11 6 6 3 50 2 4 6 Harry 5 7 3 1 33.33333333 5 6 7 Stefanie 19 3 4 2 50 2 2.5 3
