GROUPAVG

Syntax

GROUPAVG (Dimension to Group, Expression, Association 1, {Association 2 ...})

Description

Use the GROUPAVG function to average information by group. Expression contains the data to sum. The Assocation(s) indicate for which group(s) to average.

Example

Suppose that you want to average employee salaries by department. Create an analytic model definition that contains the following data cubes:

  1. EMPLOYEE_SALARY, which uses a dimension called EMPLOYEES.

    This data cube contains the salary for each employee.

  2. AVERAGE_DEPARTMENT_SALARY, which uses a dimension called DEPARTMENTS.

    This data cube contains the average salaries for each department.

  3. An association data cube called EMPLOYEE_DEPT by performing the following:

    • Create the EMPLOYEE_DEPT data cube.

    • Format the EMPLOYEE_DEPT data cube as a member of the DEPARTMENTS dimension.

    • Attach the EMPLOYEES dimension to the EMPLOYEE_DEPT data cube.

Calculate Department Salary with the following formula:

GROUPAVG(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_DEPT)

You can read this formula as follows: Average the employees' salaries by department.

To calculate group averages of all members that meet a condition, use an IF function as the expression, with #N/A as the third argument. For example, to calculate average officer salaries by department, you could use IF(IS_OFFICER, EMPLOYEE_SALARY, #N/A) instead of EMPLOYEE_SALARY in the formula above.