Search code examples
ssasmdx

calculation not working in rollup totals

I'm trying to create a calculated member in SSAS that will give me this below:

I want to create a new measure "AdjustedQuantity" that is basically the Length * Quantity (normalized to 30). Both Length and Quantity are in the same fact table.

See image below. example: if length is 15, the quantity should be halved, since 15 is half of 30. if length is 60, quantity should be doubled, etc. The AdjustedQuantity field is what I want, but my results (shown in "MyAttempt_WRONG" column) don't give me the correct totals. Note I also have [Length] as a dimension.

Note: Length can be 30, 60, 15, 45, others. 

CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure]
 AS [Measures].[Quantity] * ([Measures].[Length]/30),

enter image description here

Solution

  • The solution which will perform best is to add a column to the DSV which is:

    Quantity * Length/30

    Then create a Sum measure on that column.

    If you must do it in MDX despite worse performance that the above try:

    CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure]
 AS SUM(
  EXISTING [Your Length Dimension].[Length].[Length].Members,
  [Measures].[Quantity] * (Your Length Dimension].[Length].CurrentMember.Member_Key/30)
);

    Basically you need to do the multiplication per Length the sum up from there. Member_Key assumes the Length dimension attribute key is the integer representation of the Length.