Average on a parent account - dimension formula

[Former Member]

Hi,

So, whenever a question is posted in this forum about having a parent do something else than adding the children, for example, average. You all say, check the MDX reference and you can do it, well, i just fineshed reading an entire book about mdx, and was able to create the following query

WITH 
  MEMBER [Measures].[avg] AS 
      Sum
      (
        [PREMISSAS]
       ,[Measures].[Periodic]
      )
    / 
      Count(Descendants([Rubricas].[H1].[premissas],1)) 
SELECT 
  {([Measures].ALLMEMBERS,[Tempo].[2011.TOTAL])} ON COLUMNS
 ,Exists({[Rubricas].MEMBERS}) ON ROWS
FROM [CONSOLIDADO]

And running this code, does the trick, there is my average for all PRIMISSAS account childrens.

Now what part of this code should i put in the dimension formula? i tried

Sum
      (
        [PREMISSAS]
       ,[Measures].[Periodic]
      )
    / 
      Count(Descendants([Rubricas].[H1].[premissas],1))

No luck.. if i write every single child id and divide it by the number of children, for example

(PERCREMESHOLD+PERCLIMDISTRIBPL+PERCLIMMAXDISTRIBEMP+PERCEMP)/4)

i'll get the average, but i want it to be dynamic, just like the code above.

Any ideas??

Leandro Brasil

[Former Member · Accepted solution]

Leandro,

MDX supports AVG as a standard function: http://msdn.microsoft.com/en-us/library/ms146067.aspx

In your case it should look like Avg(Descendants([Rubricas].[H1].[premissas],1)

Hope this helps

Madis