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. This argument can be repeated to provide multiple mappings. |
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 cannot 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
These examples each use two modules. One that contains the data to aggregate, and one that uses the AVERAGE function with this data.
The first is the Employee Salaries module. In this module, the Employees list is on rows. Several line items that contain information about each employee are on columns. The Seniority and Region line items have the list data type for the Seniority and Region lists respectively.
| Seniority | Region | Salary | Count Value | |
| Ben Blackbeard | Grade 2 | Region A | 40000 | 1 |
| Graham Greenacre | Grade 1 | Region B | 22000 | 1 |
| Lisa Limey | Grade 3 | Region C | 70000 | 1 |
| Chris Collins | Grade 4 | Region B | 140000 | 1 |
| Miranda Mauve | Grade 2 | Region A | 50000 | 1 |
| Tina Turquoise | Grade 4 | Region A | 80000 | 1 |
| Peter Plum | Grade 3 | Region A | 60000 | 1 |
| Martina Mustard | Grade 2 | Region C | 50000 | 1 |
| Brian Bluebottle | Grade 1 | Region D | 26000 | 1 |
| William White | Grade 2 | Region A | 40000 | 1 |
| Rita Redwood | Grade 1 | Region C | 24000 | 1 |
| Oswald Ochre | Grade 4 | Region A | 80000 | 1 |
| Brenda Beige | Grade 3 | Region C | 70000 | 1 |
| Roger Redshanks | Grade 1 | Region C | 24000 | 1 |
| Sally Silver | Grade 1 | Region D | 100000 | 1 |
| Gloria Gold | Grade 3 | Region B | 62000 | 1 |
Average salary by region
In this module, the Region list is on columns. On rows, a single 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 salary by region
| 58,333 | 74,667 | 47,600 | 63,000 |
Average salary by seniority
In this module, the Seniority list is on columns. On rows, a single line item contains a formula that uses AVERAGE to calculate the average salary for each seniority.
| Grade 1 | Grade 2 | Grade 3 | Grade 4 | |
Average salary by seniority
| 39,200 | 45,000 | 65,500 | 100,000 |
Average salary by region and grade
In this example, the Region list is on columns, and the Seniority list is on rows. Line items are on pages, and the selected line item contains this formula:
Employee Salaries.Salary[AVERAGE: Employee Salaries.Region, AVERAGE:Employee Salaries.Seniority]
The formula calculates the average salary for employees for each combination of region and seniority.
| Region A | Region B | Region C | Region D | |
| Grade 1 | 0 | 22,000 | 24,000 | 63,000 |
| Grade 2 | 43,333 | 0 | 50,000 | 0 |
| Grade 3 | 60,000 | 62,000 | 70,000 | 0 |
| Grade 4 | 80,000 | 140,000 | 0 | 0 |
Use average to count unique values
This example uses a formula with the AVERAGE function with the Count Value line item in the Employee Salaries module 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 would count the total number of employees for each combination of region and seniority. The formula used is:
Employee Salaries.Count Value[AVERAGE: Employee Salaries.Region, AVERAGE:Employee Salaries.Seniority]
| Region A | Region B | Region C | Region D | |
| Grade 1 | 0 | 1 | 1 | 1 |
| Grade 2 | 1 | 0 | 1 | 0 |
| Grade 3 | 1 | 1 | 1 | 0 |
| Grade 4 | 1 | 1 | 0 | 0 |
