cancel
Showing results for
Did you mean:
Frequent Visitor

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

Accepted Solutions
Highlighted
Community Support Team

## Re: quartile ranking

Hi @jr99

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

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.
5 REPLIES 5
Super User

## Re: quartile ranking

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" ) )
)```
Frequent Visitor

## Re: quartile ranking

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

Community Support Team

## Re: quartile ranking

Hi @jr99

Could you share some data which could reproduce your scenario and your desired output?

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

## Re: quartile ranking

Sample Data

 date client revenue 11/10/2018 client 1 10,000.00 12/10/2018 client 1 10,000.00 11/10/2018 client 2 10,000.00 12/10/2018 client 2 9,000.00 11/10/2018 client 3 9,000.00 12/10/2018 client 3 9,000.00 11/10/2018 client 4 10,000.00 12/10/2018 client 4 7,000.00 11/10/2018 client 5 10,000.00 12/10/2018 client 5 6,000.00 11/10/2018 client 6 10,000.00 12/10/2018 client 6 5,000.00 11/10/2018 client 7 10,000.00 12/10/2018 client 7 4,000.00 11/10/2018 client 8 10,000.00 12/10/2018 client 8 3,000.00 11/10/2018 client 9 10,000.00 12/10/2018 client 9 2,000.00 11/10/2018 client 10 10,000.00 12/10/2018 client 10 1,000.00 11/10/2018 client 11 9,000.00 12/10/2018 client 11 1,000.00 11/10/2018 client 12 5,000.00 12/10/2018 client 12 4,000.00 11/10/2018 client 13 5,000.00 12/10/2018 client 13 3,000.00 11/10/2018 client 14 5,000.00 12/10/2018 client 14 2,000.00 11/10/2018 client 15 5,000.00 12/10/2018 client 15 1,000.00 11/10/2018 client 16 1,000.00 12/10/2018 client 16 4,000.00 11/10/2018 client 17 1,000.00 12/10/2018 client 17 3,000.00 11/10/2018 client 18 1,000.00 12/10/2018 client 18 2,000.00 11/10/2018 client 19 1,000.00 12/10/2018 client 19 1,000.00 11/10/2018 client 20 500.00 12/10/2018 client 20 500.00

Some sample results here.  The idea is that the quartile is based on the ranking and not the revenue

 Client Name Revenue Ranking Quartile ranking client 1 20,000.00 1 1 client 2 19,000.00 2 1 client 3 18,000.00 3 1 client 4 17,000.00 4 1 client 5 16,000.00 5 1 client 6 15,000.00 6 2 client 7 14,000.00 7 2 client 8 13,000.00 8 2 client 9 12,000.00 9 2 client 10 11,000.00 10 2 client 11 10,000.00 11 3 client 12 9,000.00 12 3 client 13 8,000.00 13 3 client 14 7,000.00 14 3 client 15 6,000.00 15 3 client 16 5,000.00 16 4 client 17 4,000.00 17 4 client 18 3,000.00 18 4 client 19 2,000.00 19 4 client 20 1,000.00 20 4
Highlighted
Community Support Team

## Re: quartile ranking

Hi @jr99

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

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.