cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PKOG12
Regular Visitor

How do I do this; If results in Column A is in top 20%, return 1, if it is top 40%, return 2......

I am trying to use the IF and ELse to create a new column to normalise my data using percentiles. The aim is to allocate a number to the top 20%, 40%, 60%....

1 REPLY 1
DataInsights
Super User
Super User

@PKOG12, try this:

 

Create measures below:

 

Customer Percentile 20% = CALCULATE ( PERCENTILE.INC ( Sales[Amount], .20 ), ALL ( Sales[Customer] ) )

Customer Percentile 40% = CALCULATE ( PERCENTILE.INC ( Sales[Amount], .40 ), ALL ( Sales[Customer] ) )

Customer Percentile 60% = CALCULATE ( PERCENTILE.INC ( Sales[Amount], .60 ), ALL ( Sales[Customer] ) )

Customer Percentile 80% = CALCULATE ( PERCENTILE.INC ( Sales[Amount], .80 ), ALL ( Sales[Customer] ) )

Percentile Rank = 
VAR vAmount = SUM ( Sales[Amount] )
VAR vPct20 = [Customer Percentile 20%]
VAR vPct40 = [Customer Percentile 40%]
VAR vPct60 = [Customer Percentile 60%]
VAR vPct80 = [Customer Percentile 80%]
VAR vResult =
SWITCH ( TRUE(),
    vAmount >= vPct80, 1,
    vAmount >= vPct60 && vAmount < vPct80, 2,
    vAmount >= vPct40 && vAmount < vPct60, 3,
    vAmount >= vPct20 && vAmount < vPct40, 4,
    vAmount < vPct20, 5
)
RETURN
vResult

 

Result:

 

DataInsights_0-1598738902908.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.