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.