avg function

[stetou]

Hi,
I'm trying to calculate a standard deviation using the MDX function. It doesn't work, so I practice with avg function, it's suppose to be simpler....

Let's suppose I have 3 dimensions: time, contaminants and age.
I want the average calculated while user select different members.
The Measure I use is "Quantity". In analysis Services the aggregate function defined for this measure is "Sum". Here is my syntax.

Avg({([Time].currentmember,[Age].currentmember,[Contaminants].currentmember)},[Measures].[Quantity])

The result (the average) is always equal to the aggregate of [Measures].[Quantity]. It seems that this function does not use all the values but only the aggregated value. And it divides by one since it uses only one value.
How can I make this function uses all the values instead of the sum of them?
English is not my primary language so I hope it is clear enough....
thanks! stetou

 

[lincon]

you can try
Avg({([Time].members)},[Measures].[Quantity])

 

[stetou]

Answer from Mark Landry....
The problem you face with any of the statistical functions in MDX like AVG(), MEDIAN(), etc. is that it's not a precalculated measure in the cube. These functions expect a set of tuples that each resolve to a measure and the statistical function operates over the entire set. Think of a tuple as an address to a cell -- it's like the array indexes to a multidimensional array. The only difference is that we're using member names instead of numbers.

The next thing to consider is that there are many different kinds of averages. There's no such thing as *the* store average because it's unclear whether you're taking one measurement (like total sales) across 20 stores and averaging it, or are you taking hundreds of measurements say of products across the same 20 stores and averaging the sales of each of those products. You first need to understand what kind of average you want. What make MDX powerful and tricky is that it provides ways of specifying all these different kinds of averages.

The next step is to write the MDX to show you all the detailed values that you want to average:

select non empty {[Store].[Store Name.members} on rows, {[Measures].[Store Sales]} on columns from [Sales]

Yields only 13 measurements of total revenue across Time, Products, and Customers. Now to average them we do:

with member [Measures].[Store Sales Avg1] as 'Avg({[Store].[Store Name.members}, [Measures].[Store Sales])'

select {Measures].[Store Sales Avg1]} on columns from Sales

Now if we want the average revenue by product category and store, then we have to generate the set of tuples:

select non empty Crossjoin([Store].[Store Name].members, [Product].[Product Category].members) on rows,

{[Measures].[Store Sales]} on columns from [Sales]

which produces 714 measurements. To get the average, do:

with member [Measures].[Store Sales Avg2] as

'Avg(Crossjoin([Store].[Store Name].members, [Product].[Product Category].members),[Measures].[Store Sales])'

select {Measures].[Store Sales Avg2]} on columns from Sales

If you want to filter by one customer or one quarter, add it to the WHERE clause, like

... where ([Time].[1997].[Q4])

HTH, Mark