# Consolidated Average

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Consolidated Average

Is there a simple way to get a consolidated average instead of a sum?
• # RE: Consolidated Average

Hi Iroca,

If your dimension is static i.e. will not change you could just change the weightings against the elements i.e. if you have a consolidation with 10 children make the weighting of each child equal to .1.

If the dimension is not static then you will need to use rules. There are a couple of ways you can do this.

The first is to sum up all of the children and divide by the count to get an average. The downside of this rule is you have to update it if your dimension structure changes. i.e.

### Source Code

1. ['Consolidation']=C:(['a'] + ['b'] + ['c']) / PALO.ECHILDCOUNT('server/db','dimension',"Consolidation")

The second method I have used is to create a dummy consolidation that mirrors your primary consolidation. Make the rule something like this:

### Source Code

1. ['Consolidation']=C:['Dummy'] / PALO.ECHILDCOUNT('server/db','dimension',"Consolidation")

The disadvantage of this approach is you have to keep two consolidations in sync.

There are posibly ways of lso doing this via an ETL process but my experience with ETL does not stretch to these types of calculations.

Hope this helps.

Rod
• Another way is to have 3 elements

1. The basic element with normal consolidation (.e.g. Sales)
2. A counter which has a rule saying counter = n:1
3. A third element Average Sales with a rule saying Average Sales = Sales / Counter

It's a bit "wasteful" as it requires thte extra element but it's otherwise quire simple and reliable and is totaly dynamic.
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• thank guys! i think i got it now
• # Consolidated Average

What if? if I have more nodes inside my C element? I will have a distorted results, how i prevent that!!

Thanks!
Ing. Gary Rivas
------------------
[ Palo User @ ]
• # RE: Consolidated Average

I tried all this above ways and and until now has been dificult to calculate average on nodes because we have to do it from down nodes to up nodes.
Ing. Gary Rivas
------------------
[ Palo User @ ]
• # RE: Consolidated Average

Hi Rod!!!

With the first method we have diferent result then the second method you mentioned.

I need get your first results not the second for me the second not are acurate, but for dinamically number of nodes and childrens!!

Do you have an idea??
Ing. Gary Rivas
------------------
[ Palo User @ ]
• # RE: Consolidated Average

Hi Gary,

If the dimension is set up correctly both rules will return the same value.

Expanding my examples further the dimension would have the following Consolidations and base level elements.

Consolidation
a
b
c

Dummy
a
b
c

Hope this helps.

Regards,

Rod
• Hi,