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

     

ryan_mayu's avatar
ryan_mayu
Super 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
    Super User
    6 years ago

    Anonymous 

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