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
Anonymous
Not applicable

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
1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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:

2.PNG

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

 

View solution in original post

6 REPLIES 6
v-eqin-msft
Community Support
Community Support

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:

2.PNG

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

 

v-eqin-msft
Community Support
Community Support

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

 

 

customRevenue.PNG
Please tell me more data details and do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
ryan_mayu
Super User
Super User

@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

1.PNG





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

Proud to be a Super User!




Anonymous
Not applicable

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?

@Anonymous 

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

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