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.


1 Answer 1


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

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

The following measure should return what you want:

     [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. Commented Jun 21, 2012 at 19:21
  • @ChadBraun-Duin : Did you finally have time to test it?
    – Benoit
    Commented Jun 29, 2012 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. Commented Jun 29, 2012 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.