Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Then in a Table Visual put the avearge of this RANK
i.e.
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 this calculated column
RANK = RANKX ( ALL ( 'Data'[Rep] ), CALCULATE ( SUM ( 'Data'[Total] ), ALLEXCEPT ( 'Data', Data[Rep] ) ), , DESC, DENSE )
Then in a Table Visual put the avearge of this RANK
i.e.
Just adding DENSE did the trick, right.
Nopes....There were 2 other differences
1) the Table Argument of RANKX....
2) the arguments inside ALLEXCEPT should exclude Total column
Correct, Thanks again.
Also, do you happen to know how to rank the amounts without getting same ranks for similar values.
HI @iamprajot
Do you mean RANK if the SUM ( 'Data'[Total] ) is same for 2 or more REPs???
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 |
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
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.
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
That's how programmers come up with something.
Great, Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |