Search code examples
ssasmdx

How to multiply two measures prior to aggregation

I have two measures. The first is amount, and the second consist of values -1,0 and 1, so table looks like this:

Amount   Sign
--------------
400      -1
200       1
300       0

Result I want to get is 400*(-1) + 200*1 + 300*0 = -200, but I am getting (400+200+300)*(-1+1+0) = 0

This is my calculated member:

WITH 
  MEMBER [Measures].[Result]
  AS
[Measures].[Sign]*[Measures].[Amount]
select 
[Measures].[Result] on 0,
[Time].[Time].members on 1
from [MyCube]
Solution

  • In you SSAS project, go to the datasource view, for the underlying fact table add a NamedCalculation. In that do the multiplication that you explained. Now in the Cube add that as a measure. It will behave exactly like you want it to behave.