Average instead of Consolidation

Post Reply
wigglyrat
Posts: 67
Joined: Wed Sep 02, 2015 3:09 pm
OLAP Product: TM1
Version: 10_2_2
Excel Version: 2010

Average instead of Consolidation

Post by wigglyrat »

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:

Code: Select all

['Product Density Counter'] = N: If(['Product Density'] <> 0, 1, 0);
['Product Density Average']=['Product Density'] \ ['Product Density Counter'];
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.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Average instead of Consolidation

Post by lotsaram »

If version 10 you can now use the ConsolidatedAvg function
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
BariAbdul
Regular Participant
Posts: 424
Joined: Sat Mar 10, 2012 1:03 pm
OLAP Product: IBM TM1, Planning Analytics, P
Version: PAW 2.0.8
Excel Version: 2019

Re: Average instead of Consolidation

Post by BariAbdul »

Apart from what lotsaram has already suggested you,Also,Please have a go through below post:

http://www.tm1forum.com/viewtopic.php?f ... 1511#p1511 Thanks.
"You Never Fail Until You Stop Trying......"
Post Reply