DAX Percentage of users based off a calculated column

[Anonymous]

Hello,

I have a list of a few thousand users that I want to group based off a calculated column.

I want to create the following: (note, each user has their own unique calculated column result)

• Top 5% of users based off calculated column value
• Top 6%-15% users based off calculated column value
• Top 16%-51% users based off calculated column value
• Remaining  users based off calculated column value

This user list will change monthly, so I want it to actually give me the top 5% no matter what.. not just a manual formula.

I attempted to use the switch formula, but I was only able to categorize the column based off of the numeric value.. and not the top % of users.  

Thanks in advance,

Ryan

[Greg_Deckler]

Seems like an interesting problem. Can you share sample data?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Not sure how to add an attachment here... but this is essential it in ascending order.

UserList - names removed	Calculated column
User8644	36.80313375
User17013	35.60606396
User20500	34.81004103
User19850	32.3700428
User17789	27.35574811
User22591	27.30762131
User16488	27.23835013
User15770	25.9206543
User16054	25.78695572
User15884	25.29712316
User15993	25.2695319
User16824	24.80589433
User16563	24.35537261
User15894	24.33767634
User17732	23.51984606
User16045	23.4172969
User10807	23.35994523
User20076	22.87100228
User15896	22.58415034
User19485	21.83053828
User21130	21.15677461
User15983	21.13471259
User8871	20.72607361
User15876	20.60984436
User17987	20.41012125
User16226	20.3500364
User19784	19.90793115
User17530	19.69572597
User16146	19.06938297
User22931	19.01623082
User19470	18.6647573
User16740	18.44771066
User16010	18.41748009
User15922	18.31152393
User15881	18.2430582

and so on... all the way down to 25k users/rows.

I would like to find the top 5%, 6-15%, 16-51%, 50% and lower... of these 25k users based off of column B.  

Let me know if this makes sense.

Thanks,

Ryan

[Greg_Deckler · Accepted solution]

See if this works for you as a calculated column:

Category = 
VAR __tmpTable = ALL(Table5)
VAR __95thPercentile = PERCENTILEX.EXC(__tmpTable,[Column],.95)
VAR __85thPercentile = PERCENTILEX.EXC(__tmpTable,[Column],.85)
VAR __49thPercentile = PERCENTILEX.EXC(__tmpTable,[Column],.49)
RETURN
    SWITCH(
        TRUE(),
        [Column]>=__95thPercentile,"5%",
        [Column]<__95thPercentile && [Column]>=__85thPercentile,"6% - 15%",
        [Column]<__85thPercentile && [Column]>=__49thPercentile,"16% - 51%",
        "50% and lower"
    )

[Anonymous]

Worked great!  Thank you very much Greg.