Sql-server – MDX – Sum values greater than then a median calculated measure

mdxsql serverssas

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.

Best 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)
)

Related Solutions

Use MDX to select Dimension-Members instead of Measure Values

You can achieve your desired results by using a crossjoin (the * notation) and putting the dimensions on the columns axis. Here's an example based upon the Adventure Works SSAS DB.

select {} on 0, 
{
[Customer].[Gender].[Gender].members
*
[Date].[Calendar Year].[Calendar Year].members 
}  on 1
from [Adventure Works]

enter image description here

MDX calculated member to band a measure

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY 
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.

Best Answer

Related Solutions

Use MDX to select Dimension-Members instead of Measure Values

MDX calculated member to band a measure

Related Question