cancel
Showing results for
Did you mean:
Helper I

## Quintile a Measure

Good Evening

I currenty have a table built in power BI and i have ranked 3 areas of sales performance. At the end of the table i have created a total score then ranked the total (If that nakes sense)

The Rank and Rank Desc are all measures

1 ACCEPTED SOLUTION
Community Support

You could use the following measure:

``````Measure =
VAR _table =
SUMMARIZE ( ALL (table1), Table1[His_UserfullName], "_Rank",  MAX(table1[rank desc]))

VAR Percentile25 =
PERCENTILEX.EXC ( _table, [_Rank], 0.25 )
VAR Percentile50 =
PERCENTILEX.EXC ( _table, [_Rank], 0.5 )
VAR Percentile75 =
PERCENTILEX.EXC ( _table, [_Rank], 0.75 )
RETURN
IF (
MAX(table1[rank desc])< Percentile25,
"Q1",
IF ( MAX(table1[rank desc])< Percentile50, "Q2", IF ( MAX(table1[rank desc])< Percentile75, "Q3", "Q4" ) )
)``````

Then you will get the below:

Wish it is helpful for you!

Best Regards

Lucien

3 REPLIES 3
Community Support

You could use the following measure:

``````Measure =
VAR _table =
SUMMARIZE ( ALL (table1), Table1[His_UserfullName], "_Rank",  MAX(table1[rank desc]))

VAR Percentile25 =
PERCENTILEX.EXC ( _table, [_Rank], 0.25 )
VAR Percentile50 =
PERCENTILEX.EXC ( _table, [_Rank], 0.5 )
VAR Percentile75 =
PERCENTILEX.EXC ( _table, [_Rank], 0.75 )
RETURN
IF (
MAX(table1[rank desc])< Percentile25,
"Q1",
IF ( MAX(table1[rank desc])< Percentile50, "Q2", IF ( MAX(table1[rank desc])< Percentile75, "Q3", "Q4" ) )
)``````

Then you will get the below:

Wish it is helpful for you!

Best Regards

Lucien

Helper I

@v-luwang-msft thnak you, works great 🙂

Super User IV

@ianboothman84 , Check if this can help

https://sqldusty.com/2018/08/31/calculating-quartiles-with-dax-and-power-bi/

Proud to be a Super User!

Announcements