cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jr99 Frequent Visitor
Frequent Visitor

quartile ranking

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: quartile ranking

Hi @jr99

 

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" ) )
    )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Super User
Super User

Re: quartile ranking

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" ) )
    )
jr99 Frequent Visitor
Frequent Visitor

Re: quartile ranking

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

Community Support Team
Community Support Team

Re: quartile ranking

Hi @jr99

 

Could you share some data which could reproduce your scenario and your desired output?

 

How to Get Your Question Answered Quickly

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jr99 Frequent Visitor
Frequent Visitor

Re: quartile ranking

Sample Data 

 

dateclientrevenue
11/10/2018client 1   10,000.00
12/10/2018client 1   10,000.00
11/10/2018client 2   10,000.00
12/10/2018client 2     9,000.00
11/10/2018client 3     9,000.00
12/10/2018client 3     9,000.00
11/10/2018client 4   10,000.00
12/10/2018client 4     7,000.00
11/10/2018client 5   10,000.00
12/10/2018client 5     6,000.00
11/10/2018client 6   10,000.00
12/10/2018client 6     5,000.00
11/10/2018client 7   10,000.00
12/10/2018client 7     4,000.00
11/10/2018client 8   10,000.00
12/10/2018client 8     3,000.00
11/10/2018client 9   10,000.00
12/10/2018client 9     2,000.00
11/10/2018client 10   10,000.00
12/10/2018client 10     1,000.00
11/10/2018client 11     9,000.00
12/10/2018client 11     1,000.00
11/10/2018client 12     5,000.00
12/10/2018client 12     4,000.00
11/10/2018client 13     5,000.00
12/10/2018client 13     3,000.00
11/10/2018client 14     5,000.00
12/10/2018client 14     2,000.00
11/10/2018client 15     5,000.00
12/10/2018client 15     1,000.00
11/10/2018client 16     1,000.00
12/10/2018client 16     4,000.00
11/10/2018client 17     1,000.00
12/10/2018client 17     3,000.00
11/10/2018client 18     1,000.00
12/10/2018client 18     2,000.00
11/10/2018client 19     1,000.00
12/10/2018client 19     1,000.00
11/10/2018client 20         500.00
12/10/2018client 20         500.00

 

Some sample results here.  The idea is that the quartile is based on the ranking and not the revenue

 

Client NameRevenue Ranking Quartile ranking
client 1   20,000.00                   11
client 2   19,000.00                   21
client 3   18,000.00                   31
client 4   17,000.00                   41
client 5   16,000.00                   51
client 6   15,000.00                   62
client 7   14,000.00                   72
client 8   13,000.00                   82
client 9   12,000.00                   92
client 10   11,000.00                 102
client 11   10,000.00                 113
client 12     9,000.00                 123
client 13     8,000.00                 133
client 14     7,000.00                 143
client 15     6,000.00                 153
client 16     5,000.00                 164
client 17     4,000.00                 174
client 18     3,000.00                 184
client 19     2,000.00                 194
client 20     1,000.00                 204
Highlighted
Community Support Team
Community Support Team

Re: quartile ranking

Hi @jr99

 

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" ) )
    )

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.