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

The noted functions all operate on the aggregated values at the current
level. If you want to average, for example, data from lower levels, you
need to use your Avg2 function that divides sums by counts, or apply the
internal avg() function to a set generated by the descendant() function.
This defeats the power of aggregations, but may be your only choice for the
median() of descendant() values.

Jim


"John Burke" <john.burke (AT) pnl (DOT) gov> wrote

It depends upon which median you want. There's a big difference
between the median store sales and the median sales for each store
across Product, Time, Customers, and Promotions.

The median store sales is simply:
Median([Store].[Store Name].Members, [Measures].[Store Sales]) =>
$52,644.07

But by the form of the MDX statement I assume you want the median
sales for each store. This requires generating a set of leaf-level
tuples. For example, Store 6 has a count of 6815 so the median
function has to operate over a set of 6815 tuples. This set is derived
using a non-empty cross-join of the leaf-level members of the Product,
Time, Customers, and Promotions dimensions. These dimensions including
Store form the primary key of the fact table.

As you can imagine this is not a quick query and it only gets worse as
the cube gets larger. Here's the MDX and the result set:

With
MEMBER [Measures].[Sales Avg3] AS
'AVG(NonEmptyCrossjoin(Descendants([Customers].[All
Customers],,LEAVES), Descendants([Product].[All Products],,LEAVES),
Descendants([Promotions].[All Promotions],,LEAVES),
Descendants([Time].DefaultMember,,LEAVES)), [Measures].[Store Sales])'
MEMBER [Measures].[Sales Median] AS
'MEDIAN(NonEmptyCrossjoin(Descendants([Customers].[All
Customers],,LEAVES), Descendants([Product].[All Products],,LEAVES),
Descendants([Promotions].[All Promotions],,LEAVES),
Descendants([Time].DefaultMember,,LEAVES)), [Measures].[Store Sales]
)'
MEMBER [Measures].[Sales Count2] AS
'NonEmptyCrossjoin(Descendants([Customers].[All Customers],,LEAVES),
Descendants([Product].[All Products],,LEAVES),
Descendants([Promotions].[All Promotions],,LEAVES),
Descendants([Time].DefaultMember,,LEAVES)).Count'
Select
{ [Measures].[Store Sales],
[Measures].[Sales Count],
[Measures].[Sales Count2],
[Measures].[Sales Average],
[Measures].[Sales Avg3],
[Measures].[Sales Median]
} on columns,
NON EMPTY { [Store].[Store Name].Members} ON ROWS
From Sales
---
Measures
Store Store Sales
Sales Count
Sales Count2
Sales Average
Sales Avg3
Sales Median
Store 19 0
Store 20 0
Store 9 0
Store 21 0
Store 1 0
Store 5 0
Store 10 0
Store 8 0
Store 4 0
Store 12 0
Store 18 0
HQ 0
Store 6 $45,750.24 6815 6815 6.71 $6.71 $6.32
Store 7 $54,545.28 8207 8207 6.65 $6.65 $6.30
Store 24 $54,431.14 8095 8094 6.72 $6.72 $6.36
Store 14 $4,441.18 1325 1325 3.35 $3.35 $2.84
Store 11 $55,058.79 8264 8263 6.66 $6.66 $6.24
Store 13 $87,218.28 13347 13340 6.53 $6.54 $5.96
Store 2 $4,739.23 1380 1380 3.43 $3.43 $2.95
Store 3 $52,896.30 7876 7872 6.72 $6.72 $6.36
Store 15 $52,644.07 7956 7956 6.62 $6.62 $6.04
Store 16 $49,634.46 7397 7390 6.71 $6.72 $6.36
Store 17 $74,843.96 11184 11173 6.69 $6.70 $6.33
Store 22 $4,705.97 1339 1339 3.51 $3.51 $2.97
Store 23 $24,329.23 3652 3651 6.66 $6.66 $6.16

Note that in some cases [Sales Count2] doesn't exactly match the
[Sales Count] that's derived from the fact table. This is because
there are some dimension keys that rollup to the same member name in
the Customer and Promotions dimensions. For instance, the fact tables
has two rows:

No Promotion Allen Holm Landslide Apple Butter $2.38
No Promotion Allen Holm Landslide Apple Butter $2.38

but inside the cube this is one cell:

No Promotion Allen Holm Landslide Apple Butter $4.76


HTH,
Mark

What a great response! Thanke Mark.
-John

Upon further reflection, to properly calculate the median of a
measure, the fact table must contain transaction-level data and not
summary values such as [Unit Sales] and [Store Sales] as found in the
FoodMart 2000 [Sales] cube.

In addition, the leaf-level cells of the cube must be at the same
level of granularity (detail) as the fact table itself.

A related example of this confusion is the [Sales Average] calculated
member of the [Sales] cube. At first glance it appears to provide the
average sale but it doesn't because multiple units are reported in
each fact table row.

The true average per unit sale is [Store Sales]/[Unit Sales]. I'd
consider this a bug in the cube's design.