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 have created a pivot table with the following colums
Client Name
Revenue
Ranking (using Rankx)
I would like to add an additional measure for ranking as a quartile
So that if i had 20 clients clients 1-5 would be in Quartile 1, 6-10 Quartile 2 etc.
I have tried creating a percentile as the first stage, by trying to do the MAX of ranking, however this appears not to be a valid option. Any ideas would be greatly recieved.
Solved! Go to Solution.
Hi @Anonymous
You may refer to below measure.
Rank = RANKX ( ALL ( Table1[client] ), CALCULATE ( SUM ( Table1[revenue] ) ) )
Measure = VAR _table = SUMMARIZE ( ALL ( Table1 ), Table1[client], "_Rank", [Rank] ) VAR _table1 = ADDCOLUMNS ( _table, "_Rank1", RANKX ( _table, [_Rank] ) ) VAR Percentile25 = PERCENTILEX.EXC ( _table1, [_Rank1], 0.25 ) VAR Percentile50 = PERCENTILEX.EXC ( _table1, [_Rank1], 0.5 ) VAR Percentile75 = PERCENTILEX.EXC ( _table1, [_Rank1], 0.75 ) RETURN IF ( [Rank] < Percentile25, "Q1", IF ( [Rank] < Percentile50, "Q2", IF ( [Rank] < Percentile75, "Q3", "Q4" ) ) )
Regards,
Cherie
Hello,
I'm also using the same method as mentioned above, however, my Quartile Ranks seem to be off.
My % Sale/Oppor Rank calculation is as follows in case needed to help me troubleshoot.
The Quartiles has 2 Q3's showing.
Any help is much appreciated.
Thank you in advance
something like this should work (adjust the table & column names), but I think there may be smarter way for the nested IF
Measure = VAR Sales = SUM ( 'Table'[Sales] ) VAR CustomerList = GROUPBY ( ALL ( 'Table' ), 'Table'[Client], "Val", SUMX ( CURRENTGROUP (), [Sales] ) ) VAR Percentile50 = MEDIANX ( CustomerList, [Val] ) VAR Percentile75 = MEDIANX ( FILTER ( CustomerList, [Val] >= Percentile50 ), [Val] ) VAR Percentile25 = MEDIANX ( FILTER ( CustomerList, [Val] < Percentile50 ), [Val] ) RETURN IF ( Sales >= Percentile75, "Q4", IF ( Sales >= Percentile50, "Q3", IF ( Sales >= Percentile25, "Q2", "Q1" ) ) )
Thank you that is awesome and works well, however what I am after is quartiles based on ranking so that if I have 20 items
the highest 5 by volume will be Q1
Thanks
Hi @Anonymous
Could you share some data which could reproduce your scenario and your desired output?
How to Get Your Question Answered Quickly
Regards,
Cherie
Sample Data
date | client | revenue |
11/10/2018 | client 1 | 10,000.00 |
12/10/2018 | client 1 | 10,000.00 |
11/10/2018 | client 2 | 10,000.00 |
12/10/2018 | client 2 | 9,000.00 |
11/10/2018 | client 3 | 9,000.00 |
12/10/2018 | client 3 | 9,000.00 |
11/10/2018 | client 4 | 10,000.00 |
12/10/2018 | client 4 | 7,000.00 |
11/10/2018 | client 5 | 10,000.00 |
12/10/2018 | client 5 | 6,000.00 |
11/10/2018 | client 6 | 10,000.00 |
12/10/2018 | client 6 | 5,000.00 |
11/10/2018 | client 7 | 10,000.00 |
12/10/2018 | client 7 | 4,000.00 |
11/10/2018 | client 8 | 10,000.00 |
12/10/2018 | client 8 | 3,000.00 |
11/10/2018 | client 9 | 10,000.00 |
12/10/2018 | client 9 | 2,000.00 |
11/10/2018 | client 10 | 10,000.00 |
12/10/2018 | client 10 | 1,000.00 |
11/10/2018 | client 11 | 9,000.00 |
12/10/2018 | client 11 | 1,000.00 |
11/10/2018 | client 12 | 5,000.00 |
12/10/2018 | client 12 | 4,000.00 |
11/10/2018 | client 13 | 5,000.00 |
12/10/2018 | client 13 | 3,000.00 |
11/10/2018 | client 14 | 5,000.00 |
12/10/2018 | client 14 | 2,000.00 |
11/10/2018 | client 15 | 5,000.00 |
12/10/2018 | client 15 | 1,000.00 |
11/10/2018 | client 16 | 1,000.00 |
12/10/2018 | client 16 | 4,000.00 |
11/10/2018 | client 17 | 1,000.00 |
12/10/2018 | client 17 | 3,000.00 |
11/10/2018 | client 18 | 1,000.00 |
12/10/2018 | client 18 | 2,000.00 |
11/10/2018 | client 19 | 1,000.00 |
12/10/2018 | client 19 | 1,000.00 |
11/10/2018 | client 20 | 500.00 |
12/10/2018 | client 20 | 500.00 |
Some sample results here. The idea is that the quartile is based on the ranking and not the revenue
Client Name | Revenue | Ranking | Quartile ranking |
client 1 | 20,000.00 | 1 | 1 |
client 2 | 19,000.00 | 2 | 1 |
client 3 | 18,000.00 | 3 | 1 |
client 4 | 17,000.00 | 4 | 1 |
client 5 | 16,000.00 | 5 | 1 |
client 6 | 15,000.00 | 6 | 2 |
client 7 | 14,000.00 | 7 | 2 |
client 8 | 13,000.00 | 8 | 2 |
client 9 | 12,000.00 | 9 | 2 |
client 10 | 11,000.00 | 10 | 2 |
client 11 | 10,000.00 | 11 | 3 |
client 12 | 9,000.00 | 12 | 3 |
client 13 | 8,000.00 | 13 | 3 |
client 14 | 7,000.00 | 14 | 3 |
client 15 | 6,000.00 | 15 | 3 |
client 16 | 5,000.00 | 16 | 4 |
client 17 | 4,000.00 | 17 | 4 |
client 18 | 3,000.00 | 18 | 4 |
client 19 | 2,000.00 | 19 | 4 |
client 20 | 1,000.00 | 20 | 4 |
Hi @Anonymous
You may refer to below measure.
Rank = RANKX ( ALL ( Table1[client] ), CALCULATE ( SUM ( Table1[revenue] ) ) )
Measure = VAR _table = SUMMARIZE ( ALL ( Table1 ), Table1[client], "_Rank", [Rank] ) VAR _table1 = ADDCOLUMNS ( _table, "_Rank1", RANKX ( _table, [_Rank] ) ) VAR Percentile25 = PERCENTILEX.EXC ( _table1, [_Rank1], 0.25 ) VAR Percentile50 = PERCENTILEX.EXC ( _table1, [_Rank1], 0.5 ) VAR Percentile75 = PERCENTILEX.EXC ( _table1, [_Rank1], 0.75 ) RETURN IF ( [Rank] < Percentile25, "Q1", IF ( [Rank] < Percentile50, "Q2", IF ( [Rank] < Percentile75, "Q3", "Q4" ) ) )
Regards,
Cherie
Hi,
I'm tring to recreate your measure using my data. But I'm not able to create PERCENTILE.EXC variable - getting an error while calling "_table1" into PERCENTILE.EXC funcation
getting errors, while calling _table1 and rank1
May I know the reason why I'm getting this errors?
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |