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 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. 

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

7 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

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

    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.

    % Sale/Oppor Rank =
    IF(
    NOT ( ISBLANK([% Sale/Oppor])),
    RANKX(
    FILTER(ALLSELECTED(Composite[HireDateBuckets]), NOT ( ISBLANK( [% Sale/Oppor]))),
    [% Sale/Oppor]
    )
    )

    The Quartiles has 2 Q3's showing.

    Any help is much appreciated.

    Thank you in advance