Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%....
@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:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |