SSAS - AverageOfChildren only applies to the time dimension
I've just spent a morning trying to figure out why my
average measure doesn't average but sums.
I guess I should have googled straight away. In doing so I found this forum
post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=548837&SiteID=17
Christian also mentions this behaviour but only for the other semi additve
measures, It does also apply to the AverageOfChildren measure as well http://blogs.conchango.com/christianwade/archive/2005/04/07/1252.aspx
To get wrong this I created a simple calculated measure by going into the
calculations tab. Clicking New Calculated Member, giving it a name and then
putting in an expression like,
[Measures].[Sum Of Ping Duration]/[Measures].[Pingstat Count]
Where the [Sum Of Ping Duration] is the sum of the measure I want the average
for and Pingstat Count is the number of instances of the measure.
I don't think you need to put in some divide by 0 handling because if the
count is 0 then the duration will be empty and so won't be calculated.
Happy to be told the best way to do this.
Update: I've just read Mosha's post on averages which is much more detailed and a very good read.
-