Forum Discussion
Solved
Rank by 2 groups and sort by MoveInScore
I am trying to create a DAX formula :
If Response Movein% is greater than 0.18 then Group 1
If Response Movein% is less than 0.18 then Group 2
Within Group 1 Rank (in DESC order) by MoveInScore.
Then continue the rank Group 2
My Goal is to get the end result of the far right column
| Pr_Prop_Code | Group | MoveInScore | Response Movein % | Rank |
| 21350 | 1 | 5 | 25.00% | 1 |
| 21265 | 1 | 5 | 25.00% | 2 |
| 11158 | 1 | 5 | 28.60% | 3 |
| 30223 | 1 | 5 | 22.20% | 4 |
| 10832 | 1 | 4.5 | 40.00% | 5 |
| 21254 | 1 | 4.5 | 37.50% | 6 |
| 20241 | 1 | 4.33 | 21.40% | 7 |
| 21156 | 1 | 4.33 | 25.00% | 8 |
| 10131 | 1 | 4.33 | 25.00% | 9 |
| 10601 | 1 | 4.25 | 22.20% | 10 |
| 10894 | 2 | 5 | 16.00% | 11 |
| 10451 | 2 | 5 | 0.00% | 12 |
| 30480 | 2 | 4.92 | 13.30% | 13 |
| 10614 | 2 | 4.91 | 17.20% | 14 |
| 10699 | 2 | 4.8 | 15.00% | 15 |
| 20370 | 2 | 4.8 | 17.10% | 16 |
| 21326 | 2 | 4.75 | 0.00% | 17 |
| 10432 | 2 | 4.75 | 11.10% | 18 |
| 21318 | 2 | 4.65 | 4.80% | 19 |
| 10137 | 2 | 4.43 | 15.20% | 20 |
| 10830 | 2 | 4.33 | 14.30% | 21 |
| 10245 | 2 | 4.33 | 8.10% | 22 |
| 20463 | 2 | 4.33 | 15.00% | 23 |
| 20177 | 2 | 4.29 | 6.90% | 24 |
4 Replies
why 21350 is the first and 21265 is the second? they are in the same group and have the same moveinsocre and same response movein%
Ideally it should look like below:
MoveinScore determines the main ranking (then if possible, to rank by the Respone Movein %)
Thank you so much Thxalot
This is awesome. great help.
I modified your formula slightly (because the Response Rate and Score are DAX measures.
This is the modified formula I used:
Rank by GroupsAB by Movein Score by Movein Response % =VAR _prod =ALLSELECTED(Property_Unit[Pr_Prop_Code])RETURNRANKX(_prod,CALCULATE((IF([Response Movein %] > 0.18, 1, 0)) * 100 + [AvgMoveInsScore],ALLEXCEPT(Property_Unit, Property_Unit[Pr_Prop_Code])))
