We are using SQL Server 2000 Analysis Services. We would like to use
the median() function, but we are having trouble getting the results
we expected. We have been testing with Avg() since it is easy to
verify. If you run the following query, you will see that we are
getting good results for Sales Avg2, but Sales Avg3, Sales Median and
Sales Count2 are not what we hoped for. The avg(), Median() and
count() functions seem to only be looking at a single value instead of
the detailed row level information. I'm hoping there is a easy way to
solve this problem.

The MDX:
--[FoodMart 2000]
With
MEMBER [Measures].[Sales Avg2] AS '[Measures].[Store
Sales]/[Measures].[Sales Count]', format='0.00'
MEMBER [Measures].[Sales Avg3] AS
'AVG({[Store].CurrentMember},[Measures].[Store Sales] )'
MEMBER [Measures].[Sales Median] AS
'MEDIAN({[Store].CurrentMember},[Measures].[Store Sales] )'
MEMBER [Measures].[Sales Count2] AS
'Count({[Store].CurrentMember})'
Select
{ [Measures].[Store Sales],
[Measures].[Sales Count],
[Measures].[Sales Count2],
[Measures].[Sales Average],
[Measures].[Sales Avg2],
[Measures].[Sales Avg3],
[Measures].[Sales Median]
} on columns,
NON EMPTY { [Store].[Store Name].Members} ON ROWS
From Sales

Any help would be appreciated.

Thanks,
-John