# MDX - Sum values greater than then a median calculated measure

I'm relatively new to SSAS / MDX. I have a cube with a calculated field which calculates the Median value for a range of prices.

``````MEDIAN( [DimItems].[Dim Items Id].CurrentMember.Children, [Measures].[Price] )
``````

Included as a measure is a quantity field. I need to calculate the sum of that quantity field where the raw price is greater than, equal to, and less than the calculated Median price.

Take the following data:

``````Qty Price
183 \$100
51  \$114
420 \$123
80  \$137
12  \$144
200 \$147
51  \$152
``````

The Median price is \$137. Therefore, to get the sum of the quantity where the Price is less than the Median, I need to sum up the following Qty values (183,51,420) since \$100, \$114, and \$123 are all less than \$137. The value of this summation is 654.

The problem is I have no idea how to do this. I cannot figure out how to compare a raw value (eg \$114) with a Median value over a range (eg \$137). Any help would be appreciated.

You need first to define a measure that will compte the median price across the siblings:

``````[Measures].[Siblings_median_price]
AS
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])
``````

The following measure should return what you want:

``````[Measures].[Sum_below_median]
AS
SUM(
[DimItems].[Dim Items Id].CurrentMember.Children
, IIF([Measures].[Price] < [Measures].[Siblings_median_price]
, [Measures].[Quantity], Null)
)
``````
• Thanks. I haven't had a chance to try this out today. I should have an update on my progress tomorrow, though. Jun 21 '12 at 19:21
• @ChadBraun-Duin : Did you finally have time to test it? Jun 29 '12 at 7:57
• I haven't gotten it to work. Maybe I'm not waiting long enough but after a few minutes of waiting for it to finish executing, I give up and cancel it. Regardless of if it will actually calculate the correct answer or not, that kind of performance will not be acceptable to users. Do you have any other ideas? Feel free to use Adventure Works - you don't have to strictly follow my example. Jun 29 '12 at 20:53