1 2 Previous Next 18 Replies Latest reply on Dec 18, 2018 2:11 AM by Matt East

# Calculation of 95th percentile

Hi,

I am trying to find 95th percentile for my report and I tried threads available on forum but I am not able to use them correctly. Can someone help me in this calculation. I have attached a sample workbook. I need to calculate P95 for Open days by Functional Groups in Sheet 1 and by Functional Groups and Sub Groups in sheet 2.

For Sheet 1, I should get 344 for Network and 253 for software.

All help is highly appreciated.

• ###### 1. Re: Calculation of 95th percentile

Hi Brijesh,

I am getting the exact value by putting "Days Open" field & then Right click on it & go to Measure-> Percentile-> 95. Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 2. Re: Calculation of 95th percentile

Hi Prashant.

This is a feature available in version 8.1 and I am on 8.0. So I will need to calculate a final value using calculations only.

Can you please tell me a way to sort the rows on basis of Fucntional Group and then find 0.95*total records and finally showing that nth record against the functional group. I tried to follow this link but unable to use it correctly in my workbook.

Thanks,

Brijesh

• ###### 3. Re: Re: Calculation of 95th percentile

Here you go.

Points to note:

1) I copied in the formula from that thread as a new calculated field.

2) I included [Days Open] as a dimension on level of detail (the calculation needs to see all of the distinct values for the field for which you want the percentile).

3) I set the Compute Using for the percentile calculation to [Days Open].

4) I added another calculated field [Is First?] to allow filtering down to just a single row per partition, after calculating the percentile. That just checks to see if the row is the first row in the partition. Note that the compute using setting must be the same on that field as on the percentile calculation.

By the way, there's a slightly updated version of the description of that calculation in the Tableau Calculation Reference Library, here.

• ###### 4. Re: Calculation of 95th percentile

Thanks so much Rick. You are a genius .

• ###### 5. Re: Re: Calculation of 95th percentile

Hi Rick,

I am trying to calculate total for percentile now, but when I go to Analysis and choose the option to show total, it is returning me a number of records. Can you please tell me how to calculate total in same workbook.

Thanks,

Brijesh.

• ###### 6. Re: Re: Calculation of 95th percentile

I've added a second copy of the percentile calculation onto the sheet - this time with the partitioning set differently so that it calculates the overall 95th percentile. I haven't tried to format it nicely.

• ###### 7. Re: Calculation of 95th percentile

Thanks Rick. This kind of solves my purpose. But my main requirement is to show the toal P95 in the same column. Like enabling Sub Toatal and Grand Total from Analysis tab. Is there a possibility to do it.

Regards,

Brijesh

• ###### 8. Re: Calculation of 95th percentile

I've tried the forumulas in the forum but not able to get it to work properly - I would like to calculate P95 similar to how other functions (average, sum, median, etc) are summarised by group (Dimension) in a single column.

Thanks

• ###### 9. Re: Re: Calculation of 95th percentile

Attached.

I just used the calculation from the reference library then had to do the following:

1) Add Days Open as a dimension on level of detail.

2) Set the partitioning on the percentile function to Days Open.

3) Added a filter on calculated field [First Row] so that only one rows is displayed per Category.

• ###### 10. Re: Re: Calculation of 95th percentile

Hi Richard,

Thanks so much for this!  I'm almost there with getting this to work except on thing.

I seem to be having some problem with the 3rd step of adding a filter on the calculated field [First Row].  My filter is only showing available value of 'True'.  Any ideas why?

Currently the columns are displaying all the data as opposed to only one row per Category.

Thanks

• ###### 11. Re: Re: Calculation of 95th percentile

Sorry, I should have mentioned that. The partitioning on the [First Row] field on the filter shelf needs to be set the same as it is on the percentile - ie to [Days Open]. That makes the filter take the first row out of each of the partitions used for the percentile calculation - so that you get one row per category. The default partitioning is making each row into a separate partition, so with only one row per partition everything is the first row, which is why the filter only offers a value of true.

• ###### 12. Re: Re: Calculation of 95th percentile

Hi Richard,

I attempted adopting your model solution to my dataset but find i cannot make it work for my dimensions. At a high level I am attempting to know what is 95th percentile of say a number of products sold for a given city. I have many cities and many products and want to see which products fall in which percentile  per city in other words for each product i want to know which cities hit 95 percentile and which 40 the percentile etc. I would like to see the value of that percentile mark to then make decisions for ranking; The trouble i seem to be running to is aggregations (I think) . Each of my 'cells'  ( or given product numbers per city) is a COUNTD(number of products) for say a year but when I plug in COUNTD into your formula that uses {Days Open] the calculation from the reference library _ I am not getting meaningful results. not sure why • ###### 13. Re: Re: Calculation of 95th percentile

If you can attach a sample workbook showing what you have done I'll try to take a look - no promises about how quickly though!

• ###### 14. Re: Re: Calculation of 95th percentile

Hi Richard,

I have been following your worked examples (very helpful, thank you!). My problem is slightly different. I want the percentile (90% in my case) to be determined by the value of the measure not the count. In the attached dummy data there are a bunch of Purchase Orders (POs) and i want to have a single cell KPI that says 'what is the value of 90% of POs '. As you can see from tab 'old version' the correct answer should be 9.51. However i want this displayed as a single cell in a dashboard. I have tried to use your formulaes and method in the tab 'new version', with suitable modifications but it is not working.

Would you be able to help with this?

Thanks,

Matt

1 2 Previous Next