cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

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

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

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Super User III
Super User III

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

Try my new Power BI game Cross the River

View solution in original post

11 REPLIES 11
Highlighted
Super User III
Super User III

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
)
Try my new Power BI game Cross the River
Highlighted
Super User III
Super User III

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

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Responsive Resident
Responsive Resident

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

Just adding DENSE did the trick, right.

Highlighted
Super User III
Super User III

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

 

 

Try my new Power BI game Cross the River
Highlighted
Responsive Resident
Responsive Resident

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.

Highlighted
Super User III
Super User III

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???

 

 

Try my new Power BI game Cross the River
Highlighted
Responsive Resident
Responsive Resident

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

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
Try my new Power BI game Cross the River
Highlighted
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors