cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamprajot Established Member
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

OrderDateRegionRepItemUnitsUnit CostTotal
1/6/16EastJonesPencil951.99189.05
1/23/16CentralKivellBinder5019.99999.5
2/9/16CentralJardinePencil364.99179.64
2/26/16CentralGillPen2719.99539.73
3/15/16WestSorvinoPencil562.99167.44
4/1/16EastJonesBinder604.99299.4
4/18/16CentralAndrewsPencil751.99149.25
5/5/16CentralJardinePencil904.99449.1
5/22/16WestThompsonPencil321.9963.68
6/8/16EastJonesBinder608.99539.4
6/25/16CentralMorganPencil904.99449.1
7/12/16EastHowardBinder291.9957.71
7/29/16EastParentBinder8119.991619.19
8/15/16EastJonesPencil354.99174.65
9/1/16CentralSmithDesk2125250
9/18/16EastJonesPen Set1615.99255.84
10/5/16CentralMorganBinder288.99251.72
10/22/16EastJonesPen648.99575.36
11/8/16EastParentPen1519.99299.85
11/25/16CentralKivellPen Set964.99479.04
12/12/16CentralSmithPencil671.2986.43
12/29/16EastParentPen Set7415.991183.26
1/15/17CentralGillBinder468.99413.54
2/1/17CentralSmithBinder87151305
2/18/17EastJonesBinder44.9919.96
3/7/17WestSorvinoBinder719.99139.93
3/24/17CentralJardinePen Set504.99249.5
4/10/17CentralAndrewsPencil661.99131.34
4/27/17EastHowardPen964.99479.04
5/14/17CentralGillPencil531.2968.37
5/31/17CentralGillBinder808.99719.2
6/17/17CentralKivellDesk5125625
7/4/17EastJonesPen Set624.99309.38
7/21/17CentralMorganPen Set5512.49686.95
8/7/17CentralKivellPen Set4223.951005.9
8/24/17WestSorvinoDesk3275825
9/10/17CentralGillPencil71.299.03
9/27/17WestSorvinoPen761.99151.24
10/14/17WestThompsonBinder5719.991139.43
10/31/17CentralAndrewsPencil141.2918.06
11/17/17CentralJardineBinder114.9954.89
12/4/17CentralJardineBinder9419.991879.06
12/21/17CentralAndrewsBinder284.99139.72

 

Calculation Table

RepSum of Total (DESC)Ranks based on Sum of Total
Kivell3109.441
Parent3102.32
Jardine2812.193
Jones2363.044
Gill1749.875
Smith1641.436
Morgan1387.777
Sorvino1283.618
Thompson1203.119
Howard536.7510
Andrews438.3711

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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.

 

rank.png

Super User
Super User

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

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

11 REPLIES 11
Super User
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
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.

 

rank.png

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

 

 

iamprajot Established Member
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
Super User

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

HI @iamprajot

 

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

 

 

iamprajot Established Member
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.

 

OrderDateRegionRepItemUnitsUnit CostTotalRanking
########CentralJardineBinder9419.9918791
########EastParentBinder8119.9916192
########CentralSmithBinder871513053
########EastParentPen Set7415.9911834
########WestThompsonBinder5719.9911395
########CentralKivellPen Set4223.9510066
########CentralKivellBinder5019.9910007
########WestSorvinoDesk32758258
########CentralGillBinder808.997199
########CentralMorganPen Set5512.4968710
########CentralKivellDesk512562511
########EastJonesPen648.9957512
########CentralGillPen2719.9954013
########EastJonesBinder608.9953914
########CentralKivellPen Set964.9947915
########EastHowardPen964.9947915
########CentralJardinePencil904.9944917
########CentralMorganPencil904.9944917
########CentralGillBinder468.9941419
########EastJonesPen Set624.9930920
########EastParentPen1519.9930021
########EastJonesBinder604.9929922
########EastJonesPen Set1615.9925623
########CentralMorganBinder288.9925224
########CentralSmithDesk212525025
########CentralJardinePen Set504.9925026
########EastJonesPencil951.9918927
########CentralJardinePencil364.9918028
########EastJonesPencil354.9917529
########WestSorvinoPencil562.9916730
########WestSorvinoPen761.9915131
########CentralAndrewsPencil751.9914932
########WestSorvinoBinder719.9914033
########CentralAndrewsBinder284.9914034
########CentralAndrewsPencil661.9913135
########CentralSmithPencil671.298636
########CentralGillPencil531.296837
########WestThompsonPencil321.996438
########EastHowardBinder291.995839
########CentralJardineBinder114.995540
########EastJonesBinder44.992041
########CentralAndrewsPencil141.291842
########CentralGillPencil71.29943
Super User
Super User

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

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