1

I need to create a calculated measure Internal Quantity for the following equation,

Internal quantity = Quantity where [X].[Category Id] = A or B/ Quantity

The calculated measure Quantity calculates the SUM of a column. So, for example if the values for [X].[Category Id].&[A] = 50 and [X].[Category Id].&[A] = 20 and the Quantity = 100, the Internal Quantity should be 0.5 and 0.2 respectively.

I have written the following MDX expression to achieve this,

Case [X].[Category Id]
   When [X].[Category Id].&[A] Then [X].[Category Id].&[A]/[Measures]. 
   [Quantity]
   When [X].[Category Id].&[B] Then [X].[Category Id].&[B]/[Measures]. 
   [Quantity]
   else NULL
   End

However with the above expression both the [X].[Category Id].&[A] and [Measures].[Quantity] return the same value probably as they are inside a case statement.

I would really appreciate any help with this. Thanks in advance!

Krishi H
  • 526
  • 8
  • 26
  • If I understand correctly then different categories requires different calculation over the measure. Do you think you can achieve this via the underlying SQL (Either way the result would be [Internal Quantity] = [quantity] / Category_div, and for categories which are not A or B it's NULL). Alternatively and in order to DEBUG, get the results of the Category_ID and Measure.quantity on your MDX just to find out what's going on – Hila DG Sep 15 '19 at 12:18
  • It may be worth looking at using `.CurrentMember` in this situation, rather than specifically mentioning [A] and [B] every time. I'm sorry I cannot be any more helpful than that. You might be able to find examples of CurrentMember being used in similar situations. – Magnus Smith Sep 25 '19 at 15:26

0 Answers0