Evaluates a given expression (or the current measure), across all elements of a set, and returns the value that is at a given percentile level.
Example
For reference, the following query shows the Patient Count measure for the members of the aged.year level. The ORDER function sorts these members into order by their value of Patient Count so that we can easily compare the later results to this query:
SELECT MEASURES.[%COUNT] ON 0, ORDER(birthd.year.MEMBERS,MEASURES.[%COUNT],BASC) ON 1 FROM patients
Patient Count
1 1916 1
2 1921 1
3 1922 1
4 1925 1
5 1941 1
6 1914 2
...
82 1967 18
83 1969 18
84 1973 18
85 1978 18
86 1979 18
87 1981 18
88 2002 18
89 2009 18
90 1968 19
91 1998 21
92 1991 23
93 2003 23
94 1977 25
Next, the following query shows the 5th percentile value for these members:
SELECT MEASURES.[%COUNT] ON 0, PERCENTILE(birthd.year.MEMBERS,,5) ON 1 FROM patients
Patient Count
5 Percentile 1
That is, the 5th percentile consists of birth years that have at most 1 patient.
The following query shows the 95th percentile instead:
SELECT MEASURES.[%COUNT] ON 0, PERCENTILE(birthd.year.MEMBERS,,95) ON 1 FROM patients
Patient Count
95 Percentile 18
That is, the 95th percentile consists of birth years that have 18 or fewer patients.
For another example, we use the second argument for PERCENTILE:
SELECT PERCENTILE(birthd.year.MEMBERS,MEASURES.[%COUNT],50) ON 1 FROM patients
50 Percentile 10
For additional, similar examples, see AVG.