The issue
TM1 easily consolidates values up, but it is not native to provide an average at a consolidated level.
There are two very common examples of this which can be used illustrate solutions. Firstly in a sales cube, the Price element should not be consolidated, but rather a weighted average of sold goods. Secondly, in a forex cube, it may be useful to have the quarterly or annual exchange rate as an average of the periods within the quarter/year.
Method 1: Back calculating, weighted average
TM1 allows you to have separate rules for N level (leaf) elements and C level (consolidated) elements. This can be useful to back calculate an average such as 'price'.
Code: Select all
['Price'] = N: stet; (allow input at the lowest level)
['Qty'] = N: stet; (input at lowest level)
['Value of Sales'] = N: ['Price'] * ['Qty']; (the total value of sales for a unit is price * quantity)
['Price'] = C: ['Value of Sales'] \ ['Qty']; (the average price is the total value of sales divided by the total quanity
Method 2: Extra elements, simple average
This method works well for the forex example. In the measures dimension have three elements, Input, Counter and Average and enter any values into the Input element. Then have the following rule:
Code: Select all
['Counter']=N: if(['Input'] <> 0, 1, 0); (if there's a value in 'Input' then put a counter in, otherwise don't)
['Average'] = ['Input'] \ ['Counter']; (the average is the sum of 'Input' divided by the sum of 'Counter')
The Average at the N level will be the same as the Input, as Input/1=Input and the rule has been set to apply to both N and C levels. Consequently Average will always make sense at all levels, whereas Input will only make sense at the N level.
Method 3: Consolidations, simple average
If you wish to average along a single dimension (e.g. Months) you can average the data by changing the weighting factor of the consolidated elements. For example, to average the 12 months rolling into All Months, give each of the months a weighting of 0.0833333. You may wish to have two consolidations - All Months Average (which applies this method), and All Months, which sums up with a weighting of 1.