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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors