DeepSee MDX Reference AVG
 Search:

Returns the average value of a given expression (or of the current measure), across all elements of a set that have a non-null value for that expression.
Returned Type
This function returns a number.
Syntax and Details
`AVG(set_expression, optional_numeric_expression)`
Where:
The function evaluates the numeric value for each element of the set, ignores any elements for which this value is null, and computes the average value for the remaining elements.
If you want to include the null elements in the average, use an expression for optional_numeric_expression that replaces null values with zero values.
If the numeric value is null for all elements, the function returns null.
Example
First, the following query shows values of three measures for the members of the aged.decade level:
```SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0,
birthd.decade.MEMBERS ON 1 FROM patients
Patient Count    Encounter Count     Avg Test Score
1 1910s                         80              5,359              75.17
2 1920s                        227             12,910              74.20
3 1930s                        567             33,211              74.67
4 1940s                        724             38,420              73.39
5 1950s                      1,079             46,883              73.72
6 1960s                      1,475             57,814              74.16
7 1970s                      1,549             49,794              74.35
8 1980s                      1,333             35,919              74.13
9 1990s                      1,426             29,219              74.79
10 2000s                      1,406             20,072              74.95
11 2010s                        134              1,346              73.55```
Next, the following query shows the average values for these measures for the members of this level:
```SELECT {MEASURES.[%COUNT],MEASURES.[encounter count],MEASURES.[avg test score]} ON 0,
AVG(birthd.decade.MEMBERS) ON 1 FROM patients
Patient Count    Encounter Count     Avg Test Score
AVG                          909.09          30,086.09              74.28```
Here, each value is the average of the values in a column in the preceding query. For example, the Patient Count value is the average of the Patient Count values in the preceding query.
For another example, we use the second argument for AVG:
```SELECT AVG(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients
AVG
909.09```
The following example uses AVG in a query that does not specify a measure:
```SELECT AVG(birthd.decade.MEMBERS) ON 0 FROM patients
AVG
909.09```
In this case, the function uses %COUNT, which counts records in the fact table.
Finally, the following example uses AVG in a query that specifies a measure in the WHERE clause:
```SELECT AVG(birthd.decade.MEMBERS) ON 0 FROM patients WHERE MEASURES.[encounter count]
AVG
30,086.09```
In this case, the function uses the measure specified in the WHERE clause.