Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated column with percentile

Hi all, I am trying to create a column showing the revenue percentile of customers. I want to be able to show in my report generally how large a customer is in comparison to other customers, kind of like a peer group percentile by revenue. Does anyone know how to do this? 

 

I have a Customer slicer on my report and want when I select a single customer, I want it to display the percentile in a Card visual. I forgot to add this extra detail before. Any thoughts?

 

CustomerRevenuePercentile (desired column)
A420
B640
C1060
D1280
E15100
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous

    According to my understanding ,you want to use Card visual to display rank when the custom has been selected in slicer, right?

    You could use the following formula:

     

    Rank/Total =
RANKX (
    ALL ( CustomRevenue ),
    CALCULATE ( SUM ( CustomRevenue[Revenue] ) ),
    ,
    ASC,
    SKIP
)
    / COUNTX ( ALL ( CustomRevenue ), CustomRevenue[Customer] )

     

    My visualizations look like this:

    Are these results what you want? If you have any questions, please upload some data samples and expected output.
    Please do mask sensitive data before uploading.
     
    Best Regards,
    Eyelyn Qin

     

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    please try to create two measures

    total = sum('table'[Revenue])

Measure = 
VAR _rank=RANKX(all('table'[Customer]),[total],,ASC)
return _rank*(1/CALCULATE(DISTINCTCOUNT('table'[Customer]),all('table')))*100

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Ryan, I appreciate the help here. I just edited my post with an additional detail. I have a slicer on my report where I'd like to select a single customer (i.e. Customer A) and use the Card visual to display that this customer is in the 20th percentile of customers in terms of revenue. Any thoughts?

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        6 years ago

        Anonymous 

        I think you can apply a slicer and use card visual to show the result

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi jack421,

    Is this column of Percentile the result you want or is it a known condition? Could you explain to me the logic of last column?
    According to my understanding, the calculations will look like this:

     

     

    Rank/Total =
RANKX (
    ALLSELECTED ( CustomRevenue ),
    CALCULATE ( SUM ( CustomRevenue[Revenue] ) ),
    ,
    ASC,
    SKIP
)
    / COUNTX ( ALL ( CustomRevenue ), CustomRevenue[Customer] )
    /(4+6+10+12+15) =
DIVIDE ( CustomRevenue[Revenue], SUM ( CustomRevenue[Revenue] ) )

     

     

    Please tell me more data details and do mask sensitive data before uploading.
    Best Regards,
    Eyelyn Qin
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous

    According to my understanding ,you want to use Card visual to display rank when the custom has been selected in slicer, right?

    You could use the following formula:

     

    Rank/Total =
RANKX (
    ALL ( CustomRevenue ),
    CALCULATE ( SUM ( CustomRevenue[Revenue] ) ),
    ,
    ASC,
    SKIP
)
    / COUNTX ( ALL ( CustomRevenue ), CustomRevenue[Customer] )

     

    My visualizations look like this:

    Are these results what you want? If you have any questions, please upload some data samples and expected output.
    Please do mask sensitive data before uploading.
     
    Best Regards,
    Eyelyn Qin