The AVERAGE aggregation function takes a set of values from a source module and returns the mean average in a result module.
For example, you can use the AVERAGE function to determine the average sales revenue generated by employees.
Syntax
Values to average[AVERAGE: Mapping, AVERAGE: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| Values to average | Number | The values to return the average of based on the Mapping argument. |
| Mapping | List, date, time period | The mapping that determines which values to return the average of. Must be a dimension in both the source and target modules. You can enter multiple arguments as mappings. Arguments must be separated by a comma. |
The AVERAGE function returns a numeric result. The result line item must share all dimensions used for the Mapping arguments.
Syntax example
'Employee Details'.Sales Revenue[AVERAGE:'Employee Details'.Seniority, AVERAGE: 'Employee Details'.Region]
In this example, the average sales revenue is returned for each combination of seniority and region.
Additional information
Summary method
Average can also be used as a summary method. You can use the summary method on line items with the number data type.
Use AVERAGE with the Users list
You can reference the Users list with the AVERAGE function. However, you can't reference specific users within the Users list as this is production data.
Constraints
The dimensions of the Mapping argument must also be dimensions of the Values to average argument.
Excel equivalent
Examples
Calculate average salaries of employees
These examples each use two modules. A source module that contains the data to aggregate, and a result module that uses the AVERAGE function with this data.
Source module
The source module, Employee salaries, has Employees list on rows, four line items that contain information about each employee are on columns:
- Seniority, a List data type on the Grade list
- Region, a List data type on the Region list
- Salary, a Number data type
- Count, a Number data type
This table shows the data for the Salary and Count line items:
| Seniority | Region | Salary | Count value | |
| Alice | Grade 2 | Region A | 40,000 | 1 |
| Bob | Grade 1 | Region B | 22,000 | 1 |
| Carol | Grade 3 | Region C | 70,000 | 1 |
| Dan | Grade 4 | Region B | 140,000 | 1 |
| Ellie | Grade 2 | Region D | 50,000 | 1 |
| Felix | Grade 4 | Region A | 80,000 | 1 |
Result module - Average salary by region
This module has the list Region on columns, and a line item Average salary on rows. This line item contains a formula that uses AVERAGE to calculate the average salary in each region.
| Region A | Region B | Region C | Region D | |
Average salaryEmployee salaries.Salary[AVERAGE: Employee salaries.Region] | 60,000 | 81,000 | 70,000 | 50,000 |
Result module - Average salary by grade
This module has the list Grade on columns, and a line item Average salary on rows. This line item contains a formula that uses AVERAGE to calculate the average salary for each seniority level.
| Grade 1 | Grade 2 | Grade 3 | Grade 4 | |
Average salary by seniorityEmployee salaries.Salary[AVERAGE: Employee salaries.Seniority] | 22,000 | 45,000 | 70,000 | 110,000 |
Result module - Average salary by region and grade
This module has the list Region on columns, the list Grade on rows, and a line item Average salary on pages.
Average salary with the formula: Employee salaries.Salary[AVERAGE: Employee salaries.Region, AVERAGE: Employee salaries.Seniority]
| Region A | Region B | Region C | Region D | |
| Grade 1 | 0 | 22,000 | 0 | 0 |
| Grade 2 | 40,000 | 0 | 0 | 50,000 |
| Grade 3 | 0 | 0 | 70,000 | 0 |
| Grade 4 | 80,000 | 140,000 | 0 | 0 |
Result module - Count unique values
This module has the list Region on columns, the list Grade on rows, and a line item Count value on pages. This module is used to determine if there's an employee for each combination of region and seniority. If so, the formula returns a value of 1. If SUM were used, it'd count the total number of employees for each combination of region and seniority.
Count value with the formula: Employee salaries.Count value[AVERAGE: Employee salaries.Region, AVERAGE: Employee salaries.Seniority]
| Region A | Region B | Region C | Region D | |
| Grade 1 | 0 | 1 | 0 | 0 |
| Grade 2 | 1 | 0 | 0 | 1 |
| Grade 3 | 0 | 0 | 1 | 0 |
| Grade 4 | 1 | 1 | 0 | 0 |
