Avg

The MDX Avg function for Essbase returns the average of values found in the tuples of a set.

Syntax

Avg ( set [,numeric_value_expression [,IncludeEmpty ] ])

Parameters

set

Set specification.

numeric_value_expression

Numeric value expression (see MDX Grammar Rules). Avg() sums the numeric value expression and then takes the average.

IncludeEmpty

Use this keyword if you want to include in the average any tuples with #MISSING values. Otherwise, they are omitted by default.

Notes

The average is calculated as (sum over the tuples in the set of numeric_value_expr) / count, where count is the number of tuples in the set. Tuples with missing values are not included in count unless IncludeEmpty is specified.

The return value of Avg is #MISSING if either of the following is true:

• The input set is empty.

• All tuple evaluations result in #MISSING values.

Example

Empty Values Included in Calculation of the Average

The following query

WITH MEMBER
 [Market].[Western Avg]
AS
 'Avg ( [Market].[California]:[Market].[Nevada], [Measures].[Sales], INCLUDEEMPTY)'
SELECT
 { [Product].[Colas].children }
ON COLUMNS,
 { [Market].[West].children, [Market].[Western Avg] }
ON ROWS
FROM 
 Sample.Basic
WHERE
 ([Measures].[Sales], [Year].[Jan], [Scenario].[Actual])

returns the grid:

Table 4-38 Output Grid from MDX Example

(axis)	Cola	Diet Cola	Caffeine Free Cola
California	678	118	145
Oregon	160	140	150
Washington	130	190	#Missing
Utah	130	190	170
Nevada	76	62	#Missing
Western Avg	234.8	140	93

Western Avg for Caffeine Free Cola is 93 because the sales for all Western states is divided by 5, the number of states.

Empty Values Not Included in Calculation of the Average

The following query is the same as the above query, except that it does not use IncludeEmpty:

WITH MEMBER
 [Market].[Western Avg]
AS
 'Avg ( [Market].[California]:[Market].[Nevada], [Measures].[Sales])'
SELECT
 { [Product].[Colas].children }
ON COLUMNS,
 { [Market].[West].children, [Market].[Western Avg] }
ON ROWS
FROM 
 Sample.Basic
WHERE
 ([Measures].[Sales], [Year].[Jan], [Scenario].[Actual])

returning the grid:

Table 4-39 Output Grid from MDX Example

(axis)	Cola	Diet Cola	Caffeine Free Cola
California	678	118	145
Oregon	160	140	150
Washington	130	190	#Missing
Utah	130	190	170
Nevada	76	62	#Missing
Western Avg	234.8	140	155

Western Avg for Caffeine Free Cola is 155 because the sales for all Western states is divided by 3, the number of states that do not have empty values for Caffeine Free Cola.