3 Replies Latest reply on Sep 14, 2018 8:04 AM by zozo alio

# rank percentile with only one selected

Hi experts,

I am using superstore data. Based on the sales, I am using Rank percentile to find out who all customers falls in below ranges.

range 1: top 5 % of customers(percentile 100-95)

range 2: top 5-20 % (percentile 95-80)

range 3: 20-50 % (percentile 80-50)

range 4: 50-100% (percentile 50-0)

I want to represent this information in the below format

there should be a line with 0-100% of customers

I have a parameter which has all the customer names. and when I select Customer name (e.g: Anil Kumar Saini which has 72 percentile. this customer falls in range 2.

the triangle under the line should shift accordingly(ranging 0-100%)

Can anyone help me how to achieve this? or please suggest any other clean way to represent the same.

• ###### 1. Re: rank percentile with only one selected

Anil,

Make a calculated field:

Rank Percentage:

IF ATTR([Customer Name])=[Customer Name Parameter]

THEN

RANK(SUM({ FIXED [Customer Name]:SUM([Sales])}))/SUM({COUNTD([Customer Name])}) END

Range:

IF (1-[Rank Percentage])<0.5 THEN 'Top 51-100%'

ELSEIF  (1-[Rank Percentage])<0.8 THEN 'Top 21-50%'

ELSEIF  (1-[Rank Percentage])<0.95 THEN 'Top 6-20%'

ELSE 'Top 5%'

END

I also attached the workbook.

Hope it helps.

Michael Ye

6 of 6 people found this helpful
• ###### 2. Re: rank percentile with only one selected

Hi Anil,

Here's another way to approach it, using a dual axis and some formatting.

Hope this helps!

Santiago

• ###### 3. Re: rank percentile with only one selected

Can someone explain why the z score of 0.53 was used instead of 0.52?

0.52 differed from the ideal value by 0.0015 instead of 0.0019 so it seems like that would have been a better choice. I realize that both give the same answer when rounded to one heartbeat per minute but it still seems odd. Clean Master Facebook Lite Mathway