Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.