PERCENTILERANK (MDX)
Returned Type
This function returns a number.
Syntax and Details
PERCENTILERANK(set_expression, numeric_expression, comparison_value)
Where:
-
set_expression is an expression that evaluates to a set, typically a set of members or tuples.
-
numeric_expression is a numeric-valued expression that the function evaluates for each set element.
Typically, this expression has the form [MEASURES].[measure_name]
-
comparison_value is a numeric literal that represents the value against which the system compares the numeric expression for all set members.
The function evaluates numeric_expression for each element of the set, compares that value to comparison_value, and returns the percentile rank of the comparison value.
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
7 1918 2
8 1920 3
9 1927 3
10 1931 3
11 1934 3
12 1926 4
13 1932 4
14 1949 4
15 1955 4
16 1956 4
17 1928 5
18 1930 5
19 1937 5
20 1938 5
21 1966 5
22 1929 6
23 1940 6
24 1952 6
25 1939 7
...
93 2003 23
94 1977 25
Next, the following query shows the percentile rank of a member that has 5 patients:
SELECT PERCENTILERANK(birthd.year.MEMBERS,MEASURES.[%Count],5) ON 1 FROM patients
Rank of 5 19.68
That is, if you consider birth years that have 5 patients or fewer, these birth years constitute 19.68% of the set of years.