Cubing — Median function (MDX)

The Median function finds the median (middle) value in a specified set.

Returns: A number.

Syntax


>>-Median--(--SetExpression--+----------------------+--)-------><
                             '-,--NumericExpression-'

Parameters

SetExpression: Specifies a set of tuples across which the median is to be found.
NumericExpression: Specifies a numeric expression that, when applied to each tuple in the set, gives the values whose median is to be found. This expression is typically a measure.
If you do not provide this parameter, the Median function determines the expression to use based on the other elements of the SELECT statement in which it occurs.

Description

If the set has an odd number of values, the median is the one in the middle. If the set has an even number of values, the median is the average of the two middle values.

Example

In this example you want see the average amount of items that you had in stock for each month in 2002. To calculate this average, you need to set the value for a month to be the same as the median value of the values for the days of the month.

The following query uses the Median function to return the number of items and median number of items for each month in the year 2002. The number of items is the sum of the number of items for the days in the month, and the median number of items is the middle value found among the number of items for the days in the month.

Query

WITH 

MEMBER [Price Analysis].[Measures].[Median Number Of Items] 

   AS Median(Descendants([Price Analysis].[Time].CurrentMember, 

   [Price Analysis].[Time].[Day of Calendar Month Level (Price Analysis)]), 

   [Price Analysis].[Measures].[Number Of Items])

SELECT {[Price Analysis].[Measures].[Number Of Items], 

   [Price Analysis].[Measures].[Median Number Of Items]}  

   ON AXIS(0),

   {Descendants([Price Analysis].[Time].[All Time (Calendar)].[2002], 

   [Price Analysis].[Time].[Calendar Month Level (Price Analysis)])}  

   ON AXIS(1)

FROM [Price Analysis]

Result

Time	Number of items	Median number of items
1	6872	225
2	8830	346
3	2845	113
4	3090	120
5	2383	101.5
6	3194	121
7	3932	131
8	3188	125
9	3127	133.5
10	2998	114
11
12

Related functions

The Median function is a member of a family of aggregating functions: Aggregate, Avg, Max, Min, and Sum.