Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

bottom 10%

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!!

 

 

DealerCustomer Satisfaction
Dealer 150.6%
Dealer 272.6%
Dealer 384.9%
Dealer 474.4%
Dealer 550.8%
Dealer 694.5%
Dealer 784.9%
Dealer 877.6%
Dealer 994.8%
Dealer 1092.7%
Dealer 1164.5%
Dealer 1243.8%
Dealer 1333.8%
Dealer 1426.7%
Dealer 1543.9%
Dealer 1648.5%
Dealer 1723.8%
Dealer 1886.4%
Dealer 1994.2%
Dealer 2098.1%
Need Help
  • aj1973's avatar
    aj1973
    5 years ago

    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")
     

3 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    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")
     
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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" )

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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