Rank repeated values in increasing order i.e. 1, 2, 3 and so on

[iamprajot]

Here is the Sample Data which contains repetition of Rep Column and what I need is a Calculated Column to Rank those Rep as 1, 2, 3 and so on.

Please provide solution in 1 Calculated Column without an extra supporting Calculated Column.

OrderDate	Region	Rep	Item	Units	Unit Cost	Total
18/04/2016	Central	Andrews	Pencil	75	1.99	149.25
04/10/2017	Central	Andrews	Pencil	66	1.99	131.34
31/10/2017	Central	Andrews	Pencil	14	1.29	18.06
21/12/2017	Central	Andrews	Binder	28	4.99	139.72
26/02/2016	Central	Gill	Pen	27	19.99	539.73
15/01/2017	Central	Gill	Binder	46	8.99	413.54
14/05/2017	Central	Gill	Pencil	53	1.29	68.37
31/05/2017	Central	Gill	Binder	80	8.99	719.2
09/10/2017	Central	Gill	Pencil	7	1.29	9.03
07/12/2016	East	Howard	Binder	29	1.99	57.71
27/04/2017	East	Howard	Pen	96	4.99	479.04
02/09/2016	Central	Jardine	Pencil	36	4.99	179.64
05/05/2016	Central	Jardine	Pencil	90	4.99	449.1
24/03/2017	Central	Jardine	Pen Set	50	4.99	249.5
17/11/2017	Central	Jardine	Binder	11	4.99	54.89
12/04/2017	Central	Jardine	Binder	94	19.99	1879.1
01/06/2016	East	Jones	Pencil	95	1.99	189.05
04/01/2016	East	Jones	Binder	60	4.99	299.4
06/08/2016	East	Jones	Binder	60	8.99	539.4
15/08/2016	East	Jones	Pencil	35	4.99	174.65
18/09/2016	East	Jones	Pen Set	16	15.99	255.84
22/10/2016	East	Jones	Pen	64	8.99	575.36
18/02/2017	East	Jones	Binder	4	4.99	19.96
07/04/2017	East	Jones	Pen Set	62	4.99	309.38
23/01/2016	Central	Kivell	Binder	50	19.99	999.5
25/11/2016	Central	Kivell	Pen Set	96	4.99	479.04
17/06/2017	Central	Kivell	Desk	5	125	625
08/07/2017	Central	Kivell	Pen Set	42	23.95	1005.9
25/06/2016	Central	Morgan	Pencil	90	4.99	449.1
10/05/2016	Central	Morgan	Binder	28	8.99	251.72
21/07/2017	Central	Morgan	Pen Set	55	12.49	686.95
29/07/2016	East	Parent	Binder	81	19.99	1619.2
11/08/2016	East	Parent	Pen	15	19.99	299.85
29/12/2016	East	Parent	Pen Set	74	15.99	1183.3
09/01/2016	Central	Smith	Desk	2	125	250
12/12/2016	Central	Smith	Pencil	67	1.29	86.43
02/01/2017	Central	Smith	Binder	87	15	1305
15/03/2016	West	Sorvino	Pencil	56	2.99	167.44
03/07/2017	West	Sorvino	Binder	7	19.99	139.93
24/08/2017	West	Sorvino	Desk	3	275	825
27/09/2017	West	Sorvino	Pen	76	1.99	151.24
22/05/2016	West	Thompson	Pencil	32	1.99	63.68
14/10/2017	West	Thompson	Binder	57	19.99	1139.4

[v-shex-msft · Accepted solution]

HI @iamprajot,

Do you mean group rank? If this is a case, you can try to use following formula.

Rank =
RANKX ( FILTER ( Table, [Rep] = EARLIER ( [Rep] ) ), [Total],, ASC, DENSE )

 

Regards,

Xiaoxin Sheng

[iamprajot]

I mean Ranking within a group of duplicate values, which gives numbering to the duplicated values.
BTW it worked, although I still need to understand the logic behind the use of earlier.