Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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
v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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