Forum Discussion
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...
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
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
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
getting errors, while calling _table1 and rank1
May I know the reason why I'm getting this errors?
Thanks