Ranking of the Groups based on Sum of their Amounts in Decreasing Order

[iamprajot]

Hi

I need help with the below problem & I want the result in a Column and not as a Measure. 

This Calculated Column does the ranking of Amounts within the Group in Decreasing Order

RANKX(ALL('Data'[Total]),CALCULATE(SUM('Data'[Total]),ALLEXCEPT('Data',Data[Rep],'Data'[Total])))

What I want is ranking of the Groups based on Sum of their Amounts in Decreasing Order and in a CALCULATED COLUMN

I have pasted 2 Tables for refrence (Original Table & the Calculation Table for understanding of how I want to Rank)

OriginalTable

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

Calculation Table

Rep	Sum of Total (DESC)	Ranks based on Sum of Total
Kivell	3109.44	1
Parent	3102.3	2
Jardine	2812.19	3
Jones	2363.04	4
Gill	1749.87	5
Smith	1641.43	6
Morgan	1387.77	7
Sorvino	1283.61	8
Thompson	1203.11	9
Howard	536.75	10
Andrews	438.37	11

[Zubair_Muhammad · Accepted solution]

@iamprajot

Then in a Table Visual put the avearge of this RANK

i.e.

[Zubair_Muhammad]

@iamprajot

Try this calculated column

RANK =
RANKX (
    ALL ( 'Data'[Rep] ),
    CALCULATE ( SUM ( 'Data'[Total] ), ALLEXCEPT ( 'Data', Data[Rep] ) ),
    ,
    DESC,
    DENSE
)

[Zubair_Muhammad · Accepted solution]

@iamprajot

Then in a Table Visual put the avearge of this RANK

i.e.

[iamprajot]

Just adding DENSE did the trick, right.

[Zubair_Muhammad]

@iamprajot

Nopes....There were 2 other differences

1) the Table Argument of RANKX....
2) the arguments inside ALLEXCEPT should exclude Total column

[iamprajot]

Correct, Thanks again.

Also, do you happen to know how to rank the  amounts without getting same ranks for similar values.

[Zubair_Muhammad]

HI @iamprajot

Do you mean RANK if the SUM ( 'Data'[Total] ) is same for 2 or more REPs???

[iamprajot]

No, not as per the Sum's of their Total Amount but normally the whole table should be ranked based on their Amounts.

I am getting repeated ranks for 1 or 2.

OrderDate	Region	Rep	Item	Units	Unit Cost	Total	Ranking
########	Central	Jardine	Binder	94	19.99	1879	1
########	East	Parent	Binder	81	19.99	1619	2
########	Central	Smith	Binder	87	15	1305	3
########	East	Parent	Pen Set	74	15.99	1183	4
########	West	Thompson	Binder	57	19.99	1139	5
########	Central	Kivell	Pen Set	42	23.95	1006	6
########	Central	Kivell	Binder	50	19.99	1000	7
########	West	Sorvino	Desk	3	275	825	8
########	Central	Gill	Binder	80	8.99	719	9
########	Central	Morgan	Pen Set	55	12.49	687	10
########	Central	Kivell	Desk	5	125	625	11
########	East	Jones	Pen	64	8.99	575	12
########	Central	Gill	Pen	27	19.99	540	13
########	East	Jones	Binder	60	8.99	539	14
########	Central	Kivell	Pen Set	96	4.99	479	15
########	East	Howard	Pen	96	4.99	479	15
########	Central	Jardine	Pencil	90	4.99	449	17
########	Central	Morgan	Pencil	90	4.99	449	17
########	Central	Gill	Binder	46	8.99	414	19
########	East	Jones	Pen Set	62	4.99	309	20
########	East	Parent	Pen	15	19.99	300	21
########	East	Jones	Binder	60	4.99	299	22
########	East	Jones	Pen Set	16	15.99	256	23
########	Central	Morgan	Binder	28	8.99	252	24
########	Central	Smith	Desk	2	125	250	25
########	Central	Jardine	Pen Set	50	4.99	250	26
########	East	Jones	Pencil	95	1.99	189	27
########	Central	Jardine	Pencil	36	4.99	180	28
########	East	Jones	Pencil	35	4.99	175	29
########	West	Sorvino	Pencil	56	2.99	167	30
########	West	Sorvino	Pen	76	1.99	151	31
########	Central	Andrews	Pencil	75	1.99	149	32
########	West	Sorvino	Binder	7	19.99	140	33
########	Central	Andrews	Binder	28	4.99	140	34
########	Central	Andrews	Pencil	66	1.99	131	35
########	Central	Smith	Pencil	67	1.29	86	36
########	Central	Gill	Pencil	53	1.29	68	37
########	West	Thompson	Pencil	32	1.99	64	38
########	East	Howard	Binder	29	1.99	58	39
########	Central	Jardine	Binder	11	4.99	55	40
########	East	Jones	Binder	4	4.99	20	41
########	Central	Andrews	Pencil	14	1.29	18	42
########	Central	Gill	Pencil	7	1.29	9	43

[Zubair_Muhammad]

Hi @iamprajot

One way might be to add a new Total Column that affects the amount in negligible decimal amounts

And then use that new Column for RANKING

New Total =
RANDBETWEEN ( 1, 100 ) / 10000000

[iamprajot]

Rand gives random numbers so the ranking is not based on the Total Amount but based on the Rand Numbers, so not a permanent solution.

I have searched almost everywhere and couldn't find anything good on this.

Anyway, thanks for all your help.

Just in case if you find any alternate to this ranking of duplicate numbers, replyon this.

Thanks again, have a great day.

[Zubair_Muhammad · Accepted solution]

Hi @iamprajot

My bad......Sorry I meant .....add a random decimal point to existing Total Column

New Total =
'Data'[Total]
    + RANDBETWEEN ( 1, 100 ) / 10000000

 Now use this New Total Column for RANKING

[iamprajot]

That's how programmers come up with something.

Great, Thanks.