TM1 Forum

I need to calculate an average of multiple cells and assign that to another cell using cube rules.

The reason I cannot simply do an add and divide is because I need it to ignore cells with 0.

Ex:

Prices-
A = 10
B = 20
C = 30
D = 0

If I do a sum and divide, it is 60 / 4 = 15. What I need is 60 / 3 = 20.

I have found some references to using AVG which is an MDX function but I have not found any references to see how it would be implemented in TM1.

Any ideas?

Hi,

There are a couple of suggestions for handling averages here: http://forums.olapforums.com/viewtopic.php?f=21&t=388

Cheers,
Martin

Martin,

I read through that post. I wish there was just like an AVG or average function that would do this without me having to create a new member or rollups as my current rule is pretty complex and adding a new element would just make it longer and more complex.

Well,

A solution is to create two new columns. One will hold the initial value. The other One is a counter.

In the Rule:

You move value A to Hold_A, B to Hold_B etc.
Then Conditional Statement: When Hold_A different from 0 then Counter_A=1 otherwise 0.

Average calculation= For the consildation Only Divide Roll up Hold values by Counter then u get ur average as wished...

Hope it helps...

Description:

A=10 Hold_A=10 Counter_A=1
B=20 Hold_B=20 Counter_B=1
C=30 Hold_C=30 Counter_C=1
D=0 Hold_D=0 Counter_D=0

TotalABCD=60 TotalHold_Values=60 Total_Counter=3

Average Calculation=TotalHold_Values=60 \ Total_Counter=3 =60/3

C>

Chris,

I looked into this, but due to the complexity of the cube, I was unable to do this. I had to create a work around solution.

Average = (A + B + C + D) / (A\A + B\B + C\C +D\D) .

This provides the correct answer but is a big mess to look at in the rules as member names are quite long.