consolidated average and consolidation summing together

Post Reply
dumbom
Posts: 16
Joined: Thu Jun 27, 2013 9:07 am
OLAP Product: TM1
Version: CX 10.1 and TM1 10.0
Excel Version: 2007 and 2010
Location: South Africa

consolidated average and consolidation summing together

Post by dumbom » Mon Mar 26, 2018 8:56 am

Hi Everyone

I have a client where we multiple dimensions in the cube and multiple levels in the hierarchy in the dimensions. For the one dimension, we would like a consolidated average at the leaf level of this dimension, but when it is not the leaf level, to sum the calculated amount.

Take the example below:
Cube - P&L
month dimension - months rolling into quarters, into full year
account dimension - a P&L roll up
measures dimension - value,weight, comment

We are looking for the average of weight at the 0 level of month, but then wanting this calculation to sum up the hierarchy of the month dimension.

We have the rule as below:

weight = IF (ellev('Month',!Month) = 0
,
ConsolidatedAvg(1,'P&L',!Month,!Account,'Weight')
,
Continue
);




the rule gives the average at the 0 level of month, but then at the consolidation , it adds the underlying numbers instead of the rule derived values. The example below shows Q1 Rule calculated vs expected. anyone have an idea on how to resolve this?



Jan Feb Mar Q1
1000 500 250 300 1050
1001 600 480 600 1680
1002 700 900 300 1900
1003 800 500 700 2000
Total sales 260 2130 1900 6630
Rule derived Total sales 650 532.5 475 6630
Required Total sales 650 532.5 475 1657.5

Post Reply