Ranking using group and sum

[CEllis]

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

[Anonymous]

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

[CEllis]

@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.

[bhanu_gautam · Accepted solution]

@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
)

[CEllis]

Thanks @bhanu_gautam keep forgetting this needs to be a new column not measure to work.