Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

 

PowerbiQuartile.PNG

Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

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

Hi @Anonymous

 

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.
Anonymous
Not applicable

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

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

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.

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

umaparvathaneni_0-1643730752785.png

getting errors, while calling _table1 and rank1 

May I know the reason why I'm getting this errors?

Thanks

 



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.