cancel
Showing results for
Did you mean:
Established Member

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

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

@iamprajot

Then in a Table Visual put the avearge of this RANK

i.e.

Super User

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

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

11 REPLIES 11
Super User

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

@iamprajot

Try this calculated column

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

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

@iamprajot

Then in a Table Visual put the avearge of this RANK

i.e.

Established Member

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

Just adding DENSE did the trick, right.

Super User

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

@iamprajot

Nopes....There were 2 other differences

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

Established Member

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

Correct, Thanks again.

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

Super User

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

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

Established Member

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

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
Super User

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

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```
Established Member

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

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.