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.
Here is the Sample Data which contains repetition of Rep Column and what I need is a Calculated Column to Rank those Rep as 1, 2, 3 and so on.
Please provide solution in 1 Calculated Column without an extra supporting Calculated Column.
OrderDate | Region | Rep | Item | Units | Unit Cost | Total |
18/04/2016 | Central | Andrews | Pencil | 75 | 1.99 | 149.25 |
04/10/2017 | Central | Andrews | Pencil | 66 | 1.99 | 131.34 |
31/10/2017 | Central | Andrews | Pencil | 14 | 1.29 | 18.06 |
21/12/2017 | Central | Andrews | Binder | 28 | 4.99 | 139.72 |
26/02/2016 | Central | Gill | Pen | 27 | 19.99 | 539.73 |
15/01/2017 | Central | Gill | Binder | 46 | 8.99 | 413.54 |
14/05/2017 | Central | Gill | Pencil | 53 | 1.29 | 68.37 |
31/05/2017 | Central | Gill | Binder | 80 | 8.99 | 719.2 |
09/10/2017 | Central | Gill | Pencil | 7 | 1.29 | 9.03 |
07/12/2016 | East | Howard | Binder | 29 | 1.99 | 57.71 |
27/04/2017 | East | Howard | Pen | 96 | 4.99 | 479.04 |
02/09/2016 | Central | Jardine | Pencil | 36 | 4.99 | 179.64 |
05/05/2016 | Central | Jardine | Pencil | 90 | 4.99 | 449.1 |
24/03/2017 | Central | Jardine | Pen Set | 50 | 4.99 | 249.5 |
17/11/2017 | Central | Jardine | Binder | 11 | 4.99 | 54.89 |
12/04/2017 | Central | Jardine | Binder | 94 | 19.99 | 1879.1 |
01/06/2016 | East | Jones | Pencil | 95 | 1.99 | 189.05 |
04/01/2016 | East | Jones | Binder | 60 | 4.99 | 299.4 |
06/08/2016 | East | Jones | Binder | 60 | 8.99 | 539.4 |
15/08/2016 | East | Jones | Pencil | 35 | 4.99 | 174.65 |
18/09/2016 | East | Jones | Pen Set | 16 | 15.99 | 255.84 |
22/10/2016 | East | Jones | Pen | 64 | 8.99 | 575.36 |
18/02/2017 | East | Jones | Binder | 4 | 4.99 | 19.96 |
07/04/2017 | East | Jones | Pen Set | 62 | 4.99 | 309.38 |
23/01/2016 | Central | Kivell | Binder | 50 | 19.99 | 999.5 |
25/11/2016 | Central | Kivell | Pen Set | 96 | 4.99 | 479.04 |
17/06/2017 | Central | Kivell | Desk | 5 | 125 | 625 |
08/07/2017 | Central | Kivell | Pen Set | 42 | 23.95 | 1005.9 |
25/06/2016 | Central | Morgan | Pencil | 90 | 4.99 | 449.1 |
10/05/2016 | Central | Morgan | Binder | 28 | 8.99 | 251.72 |
21/07/2017 | Central | Morgan | Pen Set | 55 | 12.49 | 686.95 |
29/07/2016 | East | Parent | Binder | 81 | 19.99 | 1619.2 |
11/08/2016 | East | Parent | Pen | 15 | 19.99 | 299.85 |
29/12/2016 | East | Parent | Pen Set | 74 | 15.99 | 1183.3 |
09/01/2016 | Central | Smith | Desk | 2 | 125 | 250 |
12/12/2016 | Central | Smith | Pencil | 67 | 1.29 | 86.43 |
02/01/2017 | Central | Smith | Binder | 87 | 15 | 1305 |
15/03/2016 | West | Sorvino | Pencil | 56 | 2.99 | 167.44 |
03/07/2017 | West | Sorvino | Binder | 7 | 19.99 | 139.93 |
24/08/2017 | West | Sorvino | Desk | 3 | 275 | 825 |
27/09/2017 | West | Sorvino | Pen | 76 | 1.99 | 151.24 |
22/05/2016 | West | Thompson | Pencil | 32 | 1.99 | 63.68 |
14/10/2017 | West | Thompson | Binder | 57 | 19.99 | 1139.4 |
Solved! Go to Solution.
HI @iamprajot,
Do you mean group rank? If this is a case, you can try to use following formula.
Rank = RANKX ( FILTER ( Table, [Rep] = EARLIER ( [Rep] ) ), [Total],, ASC, DENSE )
Regards,
Xiaoxin Sheng
HI @iamprajot,
Do you mean group rank? If this is a case, you can try to use following formula.
Rank = RANKX ( FILTER ( Table, [Rep] = EARLIER ( [Rep] ) ), [Total],, ASC, DENSE )
Regards,
Xiaoxin Sheng
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |