Forum Discussion

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

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

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

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

Microsoft Employee

7 years ago

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

Anonymous

Not applicable

7 years ago

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