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

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

  • 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