cancel
Showing results for
Did you mean: 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%....  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:    