cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Solution Sage
Solution Sage

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors