# ConsolidatedAvg TM1 Function: How to Use, Example, and Syntax

ConsolidatedAvg is used to calculate a single consolidated average value of child values at the n level. For example, if you have three child elements with 10, 15 and 11 in a measure, the consolidated average would be (10+15+11)/3, so 12.

## Syntax of ConsolidatedAvg

In a TM1 Rule, the syntax is:

['Measure'] = C: ConsolidatedAvg(flag-value, '', Element1, Element2, Elementn, 'Measure');

And in a Turbo Integrator process, the syntax is similar:

nAverage = ConsolidatedAvg(flag-value, 'CubeName', Element1, Element2, Elementn, 'Measure');

Where:

*flag-value*is either a 0, 1, 2 or 3.- 0 use all values
- 1 use the consolidation weighting (so the -1, 0, or 1 in weighting)
- 2 ignore zero values
- 3 combines the above two (in other words, 1 and 2 are not mutually exclusive)

*CubeName*is the name of the cube you are working with. This is not required for the rule version if your rule is addressing the current cube.*Element1, Element2, Elementn*are elements or aliases from the dimensions of the cube. This should be in the exact order of the dimensions in the cube (so like they are in a DB). Note that for ConsolidatedAvg you don’t have to use a specific element in each position here. You can use an !DimName in place of a specific element.*‘Measure’*is an element from the measures dimension (yes, I know this is superfluous, but your measures dimension should be the last one in the list!)

Note that when using ConsolidatedAvg it takes the average of the n level elements, and not the immediate children where there are multiple levels in a hierarchy.

## Further Notes from IBM

IBM has provided us with the following notes. They explain some quicks about the calculation of averages in TM1 that you should be aware of:

- Suppose zero is specified as the weighting of some consolidated elements. In that case, the Tm1s.cfg configuration parameter ZeroWeightOptimization=F must be set for these elements to be included in the calculation of the average value in a consolidation. Without this configuration parameter, the elements for which the weighting is zero are eliminated from the consolidation list and are not included when calculating the average value in a consolidation.
- If you want cells containing the value zero to be included when calculating the average, UNDEFVALS must be set in the rules for the cube that is specified by the cube-name argument. This ensures that when a zero is assigned to a cell of the cube, an actual zero value is stored in the cell and the zero value is included when calculating the average value in a consolidation.
- If the rules for the cube that is specified by the cube-name argument include a SKIPCHECK statement, the average value in consolidation will ignore zeros. Remove the SKIPCHECK statement from the rule to include zeros in the consolidation average.

## Example of ConsolidatedAvg

Here, our example is to calculate the average price from the Price measure in the Subscription cube. We want to ignore zero values.

['Average Price'] = ConsolidatedAvg(2,'Subscription',!Year,!Month,!Scenario,!Department,!Product,'Price');

So if in our cube we had the following values:

Product 1 | 10 |

Product 2 | 12 |

Product 3 | 14 |

Product 4 | 0 |

Product 5 | 0 |

Product 6 | 22 |

From these, the average calculation would sum those and divide by 4 (excluding the two zeros), so calculating the average as 15.

## Usage

You can use ConsolidatedAvg in both Rules and TI Processes.