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.