Average instead of Consolidation
Posted: Mon Jul 04, 2016 11:06 am
Hello:
I am faced with a problem in TM1 whereby I am trying to get an average at a Consolidated level instead of a count.
I have a dimension 'Sales People' which at the n-level lists individual Sales People and at the C-level has a few groupings such as 'South'' which are regions to which they belong.
I have a measure 'Product Density' that is a count of how many products each sales person has sold.
If I have, for example, two sales people who are members of Region 'South' : Beth who has sold 4 products and Tom who has sold 2, I want to see 'South' with an average of 3.
I have come across an example or two of simple averages but I don't understand how to add a count of the elements Beth and Tom (2) in the consolidation to provide the correct average for this.
Visually the hierarchy is as follows:
Region - Products
South-----------3 (Average)
----|-Beth ---- 4
----|-Tom ---- 2
I started off writing this as per other examples found:
The product density counter works fine and gives me a '1' if there is a value in Product Density.
The 'Product Density Average' does not work and I am unsure of where to go from here.
Many thanks in advance.
I am faced with a problem in TM1 whereby I am trying to get an average at a Consolidated level instead of a count.
I have a dimension 'Sales People' which at the n-level lists individual Sales People and at the C-level has a few groupings such as 'South'' which are regions to which they belong.
I have a measure 'Product Density' that is a count of how many products each sales person has sold.
If I have, for example, two sales people who are members of Region 'South' : Beth who has sold 4 products and Tom who has sold 2, I want to see 'South' with an average of 3.
I have come across an example or two of simple averages but I don't understand how to add a count of the elements Beth and Tom (2) in the consolidation to provide the correct average for this.
Visually the hierarchy is as follows:
Region - Products
South-----------3 (Average)
----|-Beth ---- 4
----|-Tom ---- 2
I started off writing this as per other examples found:
Code: Select all
['Product Density Counter'] = N: If(['Product Density'] <> 0, 1, 0);
['Product Density Average']=['Product Density'] \ ['Product Density Counter'];
The 'Product Density Average' does not work and I am unsure of where to go from here.
Many thanks in advance.