Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Quintile a Measure

Good Evening    Thank you for your time.  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...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ï¼Œ

    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

v-luwang-msft's avatar
v-luwang-msft
Community Support
5 years ago

Hi Anonymous ï¼Œ

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-luwang-msft thnak you, works great 🙂