Forum Discussion
Ranking using group and sum
Afternoon,
Is it possible to use a DAX statment to do ranking over a single table that resets at each change in grouping.
Source_Table
| Id | Subject | Cycle | Result |
| 10 | Art | 1 | 34 |
| 11 | Art | 1 | 12 |
| 10 | Biology | 1 | 38 |
| 11 | Biology | 1 | 10 |
| 10 | French | 1 | 2 |
| 11 | French | 1 | 24 |
| 10 | Art | 2 | 23 |
| 11 | Art | 2 | 15 |
| 10 | Biology | 2 | 22 |
| 11 | Biology | 2 | 34 |
| 10 | French | 2 | 45 |
| 11 | French | 2 | 10 |
Outcome - The RANK column shows which id has the lowest figure in the subject,cycle group.
| Id | Subject | Cycle | Result | RANK |
| 10 | Art | 1 | 34 | 2 |
| 11 | Art | 1 | 12 | 1 |
| 10 | Biology | 1 | 38 | 2 |
| 11 | Biology | 1 | 10 | 1 |
| 10 | French | 1 | 2 | 1 |
| 11 | French | 1 | 24 | 2 |
| 10 | Art | 2 | 23 | 2 |
| 11 | Art | 2 | 15 | 1 |
| 10 | Biology | 2 | 22 | 1 |
| 11 | Biology | 2 | 34 | 2 |
| 10 | French | 2 | 45 | 2 |
| 11 | French | 2 | 10 | 1 |
Many thanks
Chris
CEllis , You can try below DAX
RANK =
RANKX(
FILTER(
Source_Table,
Source_Table[Subject] = EARLIER(Source_Table[Subject]) &&
Source_Table[Cycle] = EARLIER(Source_Table[Cycle])
),
Source_Table[Result],
,
ASC
)
4 Replies
CEllis , You can try below DAX
RANK =
RANKX(
FILTER(
Source_Table,
Source_Table[Subject] = EARLIER(Source_Table[Subject]) &&
Source_Table[Cycle] = EARLIER(Source_Table[Cycle])
),
Source_Table[Result],
,
ASC
)Thanks bhanu_gautam keep forgetting this needs to be a new column not measure to work.
Thanks for the reply from bhanu_gautam , please allow me to provide another insight:
Hi CEllis ,
Here are the steps you can follow：
1. In Power Query – Transform -- Group.
Advanced – [Subject] and [Cycle]
Operation – All Rows
2. Add Column – Custom Column – Enter code.
Table.AddIndexColumn( Table.Sort([Row],{{"Result", Order.Ascending}}), "Row Rank",1,1)3. Select [Custom] – [Rank] ,[ID] and [Row Rank].
4. Remove – [Row].
5. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous bhanu_gautam Thankyou both for your support its much appreciated.
I can get the Anonymous to work but can bhanu_gautam be used in combination because I need to allow the same values to have the same rank number.
