Calculating AVG Values

hi mates,

 

code snippet

with
  member [measures].x avg([measures].[item value])
  member measures.parentid   

       as  [department].departments].currentmember.parent.name

 member measures.childrenid as 
        generate(descendants([department].[departments].currentmember),
         [department].[departments].currentmember.name, " - ")
  member measures.childrenname as 
        generate(descendants([department].[department].currentmember),
         [department].[department].currentmember.name, " - ")
  select
        crossjoin([time].year.&[2006],  [time].[datehier].[month].members, 
      descendants([department].[2], 
      [department].[departments].currentmember, self_and_before)) on rows , 

 

  {(measures.parentid), (measures.childrenid),  (measures.childrenname),    

  [measures].x} on columns  

 

from olapdev
where ([item].[78])

 

 

this mdx query returns following result

 year
 month
 department
 department parentid
 department childrenids
 department childrennames
 itemvalue (measure value)

 

this returns sum of item value each month,year , deparmemnt.

 

but need avg of item values each month, year , department

 

please me

  thanks

 

anand

you have "item value" measure defined. add measure count aggregate function instead of sum aggregate function. have sum , count, can sum/count , calculate average. mdx start following:

 

with
  member [measures].x iif(isempty([measures].[item value count]),null,[measures].[item value]/[measures].[item value count])

SQL Server  >  SQL Server Analysis Services