bottom 10%

[Anonymous]

Hello Community,

I have a very simple dataset showing a dealer's customer satisfaction score %.  Once the data is imported into PowerBI, how can I create a new column that will auto-populate "yes" if the dealer's score is in the bottom 10% otherwise "no"??  Thanks in advance!!

Dealer	Customer Satisfaction
Dealer 1	50.6%
Dealer 2	72.6%
Dealer 3	84.9%
Dealer 4	74.4%
Dealer 5	50.8%
Dealer 6	94.5%
Dealer 7	84.9%
Dealer 8	77.6%
Dealer 9	94.8%
Dealer 10	92.7%
Dealer 11	64.5%
Dealer 12	43.8%
Dealer 13	33.8%
Dealer 14	26.7%
Dealer 15	43.9%
Dealer 16	48.5%
Dealer 17	23.8%
Dealer 18	86.4%
Dealer 19	94.2%
Dealer 20	98.1%

[Ashish_Mathur]

Hi,

How would you calculate Top/Bottom on this dataset?  Please explain and show the expected result.

[CNENFRNL]

@Anonymous , you might want to try following formulae in calculated columns

Bottom 10% = 
VAR __n = COUNTROWS ( 'Customer Satisfaction Score' ) * .1
RETURN
    IF (
        COUNTROWS (
            FILTER (
                'Customer Satisfaction Score',
                'Customer Satisfaction Score'[Customer Satisfaction]
                    < EARLIER ( 'Customer Satisfaction Score'[Customer Satisfaction] )
            )
        ) < __n,
        "Yes",
        "No"
    )

or

Bottom 10% Rankx = 
VAR __n = COUNTROWS ( 'Customer Satisfaction Score' ) * .1
VAR __rank =
    RANKX (
        'Customer Satisfaction Score',
        'Customer Satisfaction Score'[Customer Satisfaction],
        ,
        ASC
    )
RETURN
    IF ( __rank <= __n, "Yes", "No" )

[aj1973 · Accepted solution]

Hi @Anonymous 

Here is the formula, change the rate as you wish

Yes or No =

var DealerScore = Sheet1[Customer Satisfaction]

Return

IF( DealerScore < 0.5 , "Yes", "No")