Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Jun 21 '12 at 14:06

This question came from our site for professional and enthusiast programmers.

1 Answer

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)
)
share|improve this answer
    
Thanks. I haven't had a chance to try this out today. I should have an update on my progress tomorrow, though. –  Chad Braun-Duin Jun 21 '12 at 19:21
    
@ChadBraun-Duin : Did you finally have time to test it? –  Benoit 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. –  Chad Braun-Duin Jun 29 '12 at 20:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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