Median value of SSAS members in MDX

How to calculate the median value of SSAS members in MDX in a Microsoft SSAS cube? The median function, with the MEDIAN syntax, is the only SSAS function to use. The AVG function, for example, is used to calculate member averages. Rather than inventing a calculated member that will count the distinct number of members in order to average them.

Indeed, with the MDX language, to avoid complicated and useless calculations, use existing functions. It is important to be well informed about all the possibilities offered by the function set.

Training or following SSAS tutorials is therefore essential.

Calculating the median value of MDX members in an SSAS cube

Here is an example of an MDX script for use with Analysis Services. This example creates a calculated indicator based on an existing sales indicator. The formula uses the Date dimension.

It is used on an SSAS cube that calculates the median of an indicator for a dataset:

WITH MEMBER [Measures].[INDICATOR]
AS MEDIAN(
[Date].[Calendar].CurrentMember.Children,
[Measures].[ProductSales].
)
SELECT [Measures].[INDICATOR] ON 0,
NO EMPTY [Date].[Calendar].[Month] * 
[Products].[Product Categories].[Sub-Categories].MEMBERS * 
[Geography].[Geography].[Country].MEMBERS ON 1
FROM [YOUR_CUBE_SSAS]

Note that the median is done by displaying the axes of months, product sub-categories and also countries.

Definition of a median value

In mathematics, and more precisely in statistics, a median is the value that separates two sets of data. Also called a group, it is the lower and upper halves of a data set.

That is, there are as many values below the median value as there are above. This is quite different from an average because it is the number of members that is considered here, not the value of the members.

This short article on Microsoft OLAP technologies shows an example of an MDX script to calculate the median value of members calculated with SSAS.

Finally, here is a DAX formula to calculate the Year-To-Date value of a measure as the sales of the past year for example.