cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ianboothman84
Helper I
Helper I

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 the total (If that nakes sense)

 

Now i am struggling to create a quintile measure, please help 🙂

 

The Rank and Rank Desc are all measures

ianboothman84_0-1617052914538.png

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @ianboothman84 ,

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:

v-luwang-msft_0-1617261281565.png

 

 

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @ianboothman84 ,

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:

v-luwang-msft_0-1617261281565.png

 

 

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

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

amitchandak
Super User IV
Super User IV

@ianboothman84 , Check if this can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors