Hello All!

I have two dimensions: In Customer and Out Customer, both with same members (Name, etc)

I have measures: Cost, Revenue and Calculated member Margin (Cost-Margin)

Is it possible to create measure that would work on that way:

Let's say I have Customers A, B and C and this data available:

In Customer: A Revenue: 10 Out Customer: B Cost 5

In Customer: B Revenue: 10 Out Customer: B Cost 5

In Customer: C Revenue: 10 Out Customer: B Cost 5

In all cases, calculated Margin is: 5

I would like to create a new measure, let's call it MarginX that would follow this rule:

If In Customer=A and Out Customer=B MarginX should be 20% lower than Margin.

So final data would look like this:

In Customer: A Revenue: 10 Out Customer: B Cost: 5 Margin: 5 MarginX: 4

In Customer: B Revenue: 10 Out Customer: B Cost: 5 Margin: 5 MarginX: NULL

I tried many things, but I just can't get it to work (scopes, existing, etc).

Any hints?

Thank you and kind regards,
Dejan

I suppose you could figure out a way to do it in MDX but why not just do it in SQL? Can you create a view in SQL or a calculated column in your DSV do perform the MarginX calculation? If it is a leaf level calculation not one that needs to operate on aggregated values then just do it in SQL.