TM1 Forum

Firstly, i'm a relative newbie, so please forgive any poor terminology.

I have a problem with averaging share percentages in a cube. I have read various solutions on this forum, which has got me as far as i have got, but despite my being so close to the desired solution, i can't get it over the finishing line and was hoping someone would be able to help.

My actual cube has 17 dimensions, and so for ease of understanding i have simplified it in the below explanation.

Guess some people might be able to read my rule and see what i am trying to do, so i will kick off with that:

['Share %','All Months']=
IF(
ELLEV('Dim A',!Dim A) = 0 &
ELLEV('Dim B',!Dim B) = 0 &
ELLEV('Dim C',!Dim C) = 0 &
ELLEV('Dim D',!Dim D) = 0,
ConsolidatedAvg(2,'',!GBL Year,!GBL Month,!DIM X, !DIM A, !DIM B, !DIM C, !DIM D, 'Share %'),
ConsolidateChildren('DIM D')\ConsolidatedCountUnique(0,'GBL Month','',!GBL Year,!GBL Month,!DIM X, !DIM A, !DIM B, !DIM C, !DIM D, 'Share %')
);

Percentages stored in DIMs A, B, C and D all add up to 100% for DIM X. I want All Months to be the average Share % of all the months (ignoring zeros). I want people to then be able to view in a cube for any given element in X, the different combinations of shares for DIMs, A, B, C and D. This they can do fine at a monthly level, but the problems start at the All Months consolidation.

With this rule, users can view the relative shares on an 'All Month' level for whichever combination of dimensions they want in a cube view, with the exception of DIM D, which now just sums up the months to 'All Months'.

For example, they can have a view with DIM A and DIM B on the left and see all months add up the average in All Months. 'All DIM A' + 'All DIM B' adds up to 100% at the top of the columns as well. DIM X has a single element selected on the view and DIMs C and D are 'All DIM C' and 'All DIM D'.

This works perfectly for pulling in DIMs A, B or C, but because i am ConsolidatingChildren along DIM D, whenever i pull that down to the left of the view, the 'All Months' just sums up the months and doesn't do the average.

I can obviously change the ConsolidateChildren dimension to any of the other three, which then makes DIM D work, but then the new ConsolidateChildren DIM fails. I thought i could maybe add another IF statement to work with two different ConsolidateChildren calculations, such as IF( ELLEV('Dim D',!Dim D) = 0 , ConsolidateChildren('DIM C')....., ConsolidateChildren('DIM D') but that doesn't work.

Does my explanation of my problem make any sense? If it does, anyone have any ideas for solutions?

Thanks.

Unbelievable.

Having spent days on this problem, ten minutes after posting on the forum i found the solution:

['Share %','All Months']=
IF(
ELLEV('Dim A',!Dim A) = 0 &
ELLEV('Dim B',!Dim B) = 0 &
ELLEV('Dim C',!Dim C) = 0 &
ELLEV('Dim D',!Dim D) = 0,
ConsolidatedAvg(2,'',!GBL Year,!GBL Month,!DIM X, !DIM A, !DIM B, !DIM C, !DIM D, 'Share %'),
ConsolidateChildren('GBL Month')\ConsolidatedCountUnique(0,'GBL Month','',!GBL Year,!GBL Month,!DIM X, !DIM A, !DIM B, !DIM C, !DIM D, 'Share %')
);

I cannot believe i didn't try the ConsolidateChildren('GBL Month') before. I guess something else must have been different when i tried then, because it works now.

Thanks all for the time reading this post anyway. Hopefully my solution proves useful for someone else.

Hi

I am not sure that I understand what you are trying to do but I wonder if the following solution might be simpler.

Add a Count Measure.

Calculate this using a rule that says Count = 1 if value is non-zero

Calculate Average as total of values \ count

In general it is best to avoid use of the Consolidate functions unless absolutely necessary as they are relatively slow.

Regards

Paul Simon