Introduction to Summary Functions

MDX includes functions that summarize a given value, across a given set. For each function, the arguments are a set and an optional numeric expression (such as a reference to a measure). The system evaluates the expression for each member of the set and then returns a single value. If no numeric expression is given, the system instead evaluates the measure used in the query (possibly %COUNT).

The functions are as follows:

SUM, which returns the sum of the values.
AVG, which returns the average value. This function ignores members for which the expression is null.
MAX, which returns the maximum value.
MIN, which returns the minimum value.
MEDIAN, which returns the value from the set that is closest to the median value.
STDDEV, which returns the standard deviation of the values.
STDDEVP, which returns the population standard deviation of the values.
VAR, which returns the variance of the values.
VARP, which returns the population variance of the values.

For example:

SELECT MAX(diagd.diagnoses.MEMBERS,MEASURES.[%COUNT]) ON 0 FROM demomdx

                                       MAX
                                        828

This query shows the maximum value of the %COUNT measure for the members of the Diagnoses level.

For another example, use the same function without specifying its second argument. In this case, the query displays the %COUNT measure as a column:

SELECT MEASURES.[%COUNT] ON 0, MAX(diagd.diagnoses.MEMBERS) ON 1 FROM demomdx

                                    %COUNT
MAX                                     828

For another example, use the same function without specifying any measure in the query at all:

SELECT MAX(diagd.diagnoses.MEMBERS) ON 0 FROM demomdx

                                       MAX
                                        828

In this case, the system uses %COUNT.

Adding a Summary Line