90th Percentile

[jasonmacpei]

Hi Everyone,
I'm trying to come up with a calculation that will calculate the 90th percentile of a population.  The numbers I'm looking at are Turn Around Times of applications expressed in the number of days.  I would like to display the number of the dashboard that represents the turn around time at the 90th percentile.
For example, if the set was (5,5,6,7,8,8,9,10,12,15) the result would be 12.  This would then also recalulate as I apply filters to the dashboard.

I've tried all kinds of different percentile([TAT]) calcs, but can't get everything to work, especially when I try and break down the population into groups: ie: by application status: (Pending, Complete, Withdrawn).

Any help would be much apprecaited!

[bus_pass_man]

but can't get everything to work,

More information might be helpful.  What did you try?  What results did you get? 



I took your data and put it into a xls.  Val1 is the same data, just reordered.

I uploaded the file, created a module (hey I'm a modeller; that's what I do.  The report author should not need to create stuff like this.) created a percentile ( {query item name}) calculation.  I also created a stand alone filter with the expression percentile ( {query item name}) >= .9

I then brought the module into report studio and test it.  I was able to filter the report to only show the rows where the percentile was .9 or more.

I was able to do all but the latter in a dashboard without mucking about with a module.  For the latter, I needed to manually create the filter in the dashboard.


row   val   val1
1   5   15
2   5   9
3   6   8
4   7   6
5   8   7
6   8   8
7   9   10
8   10   12
9   12   5
10   15   5


Is this along the lines of what you are trying?  Is this along the lines of what you want to achieve?

Assuming you have another column with status, you could use the for operator, which you could use to determine the percentiles for your TAT by their status. 

[jasonmacpei]

Thank you for your reply!

The end result I'm looking for is a number that I can put onto the dashboard that represents the TAT in number of days. 

percentile([Business Layer].[DW_FCT_APP].[APP_TAT]

if(  [Business Layer].[DW_FCT__APP].[Percentile]  <= 0.9) then ([Business Layer].[DW_FCT__APP].[APP_TAT] )  else (null)

This is the calculations that I am trying.  It appears to work, until I try to break down the number by a dimension (like app status).  The result is return numbers that are very similar, but not always the same.)  For example, the tat on the whole population is 57 days....but, when I break it down, it comes out like:

Pending - 57
Completed - 57
Withdrawn - 58

Where the actual TAT for these numbers at the 90th percentile would be:

Pending - 46
Completed - 57
Withdrawn - 22

So, not sure what is happening, or if this is expected behavior.

Thanks again for the help