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

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