Aggregation functions in Calculated Measures displays wrong values.

Question

Hi,

I think this calculated measure implementation is making me absent minded, so if this seems like a silly question, please ignore my behaviour but do answer to my post :-)

I will ask this question with a sample data: consider that the cube consists of School Children's names as the first dimension (school_children) and date(jan, feb....) as the second dimension. the measure (M) is the 'exam scores' of the school children.

                         jan  feb mar
school_children    M   M    M
----------------------- 
tony                    50  20   40 
bony                  10   40   40   
mony                 60   60   70

Now when i add a calculated measure where I want to display the avg marks of each. so in the calculated measures formula I add:  Avg([Measures].[M]).  (This is how it is in the Oracle OLAP :-))

But this does not display the average of all tony's scores in a new column M2 (calculated measure). it just displays the same values as the measure M.

so what is happening here? how to get the average then? I do not want to use an avg Aggregation.  I thought that I would probably have to programmatically convert all avg functions to something like this:  [Measures].[M] / count([Measure].[M]=tony or soemthing like this. not sure again.

Answer 1

The Avg function receives a set as a first parameter, and the measure you want to calculate the average of as a second.

The second parameter is optional, so in this case you are saying to Analysis Services: "give me the average of the measures in the current query context for the set [Measures].[M]" That is: (Measures].[M] / 1)

 Since you want to calculate the avg along the time dimension, you should say:

Avg([Time],[2006].Members, [Measures].[M]).

For mor information on Avg see:

http://msdn2.microsoft.com/en-us/library/ms146067.aspx

and

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=390791&SiteID=1