average using SUM and dividing by non-empty count with MDX

Question

I know this should be easy but I'm new to MDX and having trouble getting it right. My goal is to get the average order cost for a specific customer, including the customer's sub-accounts in the hierarchy. I can get the average correctly using the MDX below if I wanted to count zero-dollar orders but I don't (they actually show up as empty, not zero, but don't worry about that). So how do I update the query so that it only looks at non-empty OrderCost values? The last line was a failed attempt.

WITH MEMBER avgcost as ([Measures].[OrderCost]/[Measures].[OrderCount])
SELECT avgcost on columns,
[Dim User].[User ID].&[381] on rows
from [OrdersCube]
//where Filter([Dim User].[User ID].All, [Measures].[OrderCost].Value <> "")

findango findango · Accepted Answer · 2013-03-14T05:21:57

Have you considered using the Avg() function? It ignores empty cells by default. You'll need to construct a set that describes the individual orders that you want averaged (and not the aggregated OrderCost for the one user). For example:

WITH MEMBER avgcost as Avg([Orders].Members, [Measures].[OrderCost])
SELECT 
    avgcost on columns,
    [Dim User].[User ID].&[381] on rows
from [OrdersCube]

See documentation here: http://msdn.microsoft.com/en-us/library/ms146067.aspx

average using SUM and dividing by non-empty count with MDX

1 Answers