MDX MEDIAN() Function Incorrect Results

2
votes

I am trying to implement a data cube whereby I can show the average and the median of a measure for a given set of dimensions. Basic example using AdventureWorksDW:

WITH
    MEMBER [Measures].[Median Qty] AS
     MEDIAN ( [Product].[Product Categories].siblings, [Measures].[Reseller Sales-Order Quantity])
    MEMBER [Measures].[avgOrderSize] AS
    CASE
        WHEN [Measures].[Reseller Sales Count] = 0 THEN NULL
        ELSE ([Measures].[Reseller Sales-Order Quantity]  / [Measures].[Reseller Sales Count])
    END

SELECT {[Measures].[Reseller Sales-Order Quantity], [Measures].[Median Qty], [Measures].[avgOrderSize]} ON 0,
NON EMPTY([Product].[Product Categories].[Category]) ON 1
FROM [Analysis Services Tutorial]
WHERE ([Product].[Product Name].&[476]);

The output is:

Median output

My [Measures].[avgOrderSize] is correct as it appears to be using the actual measures returned in the query. However, the [Measures].[Median Qty] is not at all correct for what my requirements are. I would have though the value would be around 6 (given that my AdventureWorksDW2012 has 344 reseller orders that include this product). It looks like the MEDIAN() is working across the entire set of products?

I have tried variations on [Product].[Product Categories].siblings including [Product].[Product Name].CurrentMember which returned the identical value as the Reseller Sales-Order Quantity What am I doing wrong and how can I get the MEDIAN() function to act like I am expecting?

2
ssasmdx
Do you have a degenerate dimension that has one dimension member for every reseller order (in your real cube I mean)? What version of SSAS and is it Enterprise edition? – GregGalloway
@GregGalloway We do have degenerate dimensions in our real data. I am hoping to understand MEDIAN in the AW data first so I have something to test and compare with. We are using SSAS 2012 Standard Edition. – wergeld

2 Answers

1
votes

Warning. This will be slow. But it should be correct. I'm afraid Median is just an expensive calculation. The first parameter should be a degenerate dimension and an attribute which has one member per row in the fact table. 

WITH
    MEMBER [Measures].[Median Qty] AS
     MEDIAN ( EXISTING [Reseller Sales].[Reseller Sales Order Number].[Reseller Sales Order Number].Members, [Measures].[Reseller Sales-Order Quantity])
    ...
0
votes

I've not played with that function yet but a couple of things you could try are:

Add the EXISTING keyword to the calculation:

WITH
    MEMBER [Measures].[Median Qty] AS
     MEDIAN ( 
        EXISTING [Product].[Product Categories].siblings
      , [Measures].[Reseller Sales-Order Quantity]
     )
...

Add the Exists function although this is probably very similar to the above:

WITH
    MEMBER [Measures].[Median Qty] AS
     MEDIAN ( 
        EXISTS([Product].[Product Categories].siblings, [Product].[Product Name].&[476])
      , [Measures].[Reseller Sales-Order Quantity]
     )
...

Try this:

WITH
    MEMBER [Measures].[Median Qty] AS
     MEDIAN ( 
        [Product].[Product Categories].currentmember.siblings
      , [Measures].[Reseller Sales-Order Quantity]
     )
...