MEDIAN (MDX)
Returned Type
This function returns a number.
Syntax and Details
MEDIAN(set_expression, optional_numeric_expression)
Where:
-
set_expression is an expression that evaluates to a set, typically a set of members or tuples.
-
optional_numeric_expression is a numeric-valued expression that the function evaluates for each set element.
Typically, this expression has the form [MEASURES].[measure_name]
If you do not specify a numeric expression, the system uses the measure used by the current result cell. For example, this might be the measure used on the 0 axis or the measure specified in the WHERE clause, if any. If the query itself does not specify a measure, the system instead uses %COUNT, which counts records in the fact table.
The function evaluates the numeric value for each element of the set, ignores any set elements for which this value is null, and finds the value that is closest to the median for the remaining elements.
To instead find the median value across the lowest-level records, use the %KPI function with the sample plug-in class %DeepSee.PlugIn.MedianOpens in a new tab. To see examples, download and set up the samples as described in Accessing the BI Samples; then display the User Portal in the namespace where you downloaded the samples and look at the sample dashboards in the KPIs & Plug-ins folder.
Example
First, the following query shows values of three measures for the members of the aged.decade level:
SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0,
birthd.decade.MEMBERS ON 1 FROM patients
Patient Count Encounter Count Avg Test Score
1 1910s 80 5,359 75.17
2 1920s 227 12,910 74.20
3 1930s 567 33,211 74.67
4 1940s 724 38,420 73.39
5 1950s 1,079 46,883 73.72
6 1960s 1,475 57,814 74.16
7 1970s 1,549 49,794 74.35
8 1980s 1,333 35,919 74.13
9 1990s 1,426 29,219 74.79
10 2000s 1,406 20,072 74.95
11 2010s 134 1,346 73.55
Next, the following query shows the average values for these measures for the members of this level:
SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0,
MEDIAN(birthd.decade.MEMBERS) ON 1 FROM patients
Patient Count Encounter Count Avg Test Score
MEDIAN 1,079 33,211 74.20
Here, each value is the median of the values in a column in the preceding query. For example, the Patient Count value is the median value of the Patient Count values in the preceding query.
For another example, we use the second argument for MEDIAN:
SELECT MEDIAN(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients
MEDIAN
1,079
For additional, similar examples, see AVG.
