Forum Discussion
Create a ranking column on a Table Visual with DAX
I have a table visual and would like to have an index column that will assist with making the ranking easier. So the table is as follows
| Name | Points | GD | ||
| John | 17 | 6 | ||
Erick | 12 | 0 | ||
| Sandra | 11 | 5 | ||
| Lisa | 11 | 3 | ||
| Wendy | 11 | 2 | ||
| Carol | 10 | 0 | ||
| Kim | 8 | 3 |
Do The Superior column is points and the next ordering one is GD. I would like to get a 1,2,3,4 .... column that once I put on the side and press sort ascending it remains in the same order. Thanks.
HI Anonymous
Understood! Please try the following Measures. It includes a small workaround (second measure). From my understanding the challenge we have is to prioritize points and if points are equal then we have to look on the GD, right?
Your Base table
first I built a measure only using points for ranking
RANK Table (only Points) = RANKX( ALL(RankingTable[Name]), CALCULATE(SUM(RankingTable[Points])) )NOw for adding the GD as a second priority I used a small trick by adding the SUM of GD and dividing it by 100 so that you get for sorting [POINTS],([GD]/100). As long the sum of GD can not be higher 100 it works. when GD can become higher, then you can also multiple 1000 or more.
RANK Table (Points and GD) = RANKX( ALL(RankingTable[Name]), CALCULATE(SUM(RankingTable[Points]) + SUM(RankingTable[GD])/100) )There might be a better solution but I am a little bit on the run but wanted to give you a solution. 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
4 Replies
Hi Anonymous
If I udnerstood correctly you can simply use the Index Column feature in Power QUery:
your base table:
now you should have a column which you can use for sorting
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hello Mikelytics , This sort of does not answer, I have a visual table. That I have , this limits the work to be done on DAX only...
HI Anonymous
Understood! Please try the following Measures. It includes a small workaround (second measure). From my understanding the challenge we have is to prioritize points and if points are equal then we have to look on the GD, right?
Your Base table
first I built a measure only using points for ranking
RANK Table (only Points) = RANKX( ALL(RankingTable[Name]), CALCULATE(SUM(RankingTable[Points])) )NOw for adding the GD as a second priority I used a small trick by adding the SUM of GD and dividing it by 100 so that you get for sorting [POINTS],([GD]/100). As long the sum of GD can not be higher 100 it works. when GD can become higher, then you can also multiple 1000 or more.
RANK Table (Points and GD) = RANKX( ALL(RankingTable[Name]), CALCULATE(SUM(RankingTable[Points]) + SUM(RankingTable[GD])/100) )There might be a better solution but I am a little bit on the run but wanted to give you a solution. 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------