MDX calculation has wrong order of precendence

Question

Im having an issue with an MDX query, and I think it boils down to the order of precedence between calculating an aggregate and a calculated member.

Let me start with the underlying data, which revolves around a valuation (which has a date, and some other data such as a member type, a scheme - and crucially for this question; a loading factor) and an associated value.

The data

Valuation Table

Id | Valuation Date | Member Type | Scheme   | Loading Factor
=============================================================
1  | 2010-01-01     | TypeA       | Scheme X | 0.02
2  | 2010-01-01     | TypeB       | Scheme X | 0.02
3  | 2010-01-01     | TypeA       | Scheme Y | 0.02
4  | 2010-01-01     | TypeB       | Scheme Y | 0.02

ValuationValue table

ValuationId | Value
====================
1           | 1000.0
2           | 2000.0
3           | 3000.0
4           | 4000.0

This, when loaded into a cube has a Valuation dimension with attributes MemberType, Scheme and date. And a cube with Measure group ValuationValue containing Value measure, and a Valuation measure group containing Loading Factor like so:

Cube
 -Measure Groups
  - Valuation
    |_Loading Factor
  - ValuationValue
    |_Value
 - Dimensions
  - Valuation
    |_MemberType
    |_Scheme
    |_Date

The question

Loading factor is used to load the Value, think of it like a tax, so 0.02 means "Loading amount is 2% of the value". When returning Value from a query, I need to also calculate the amount to load this value by. A typical query might look like

SELECT { [Measures].[Value] } ON 0, [Valuation].[Scheme] ON 1 FROM Cube

This would return 2 rows, and as you can see by comparing to the data above it correctly sums across memberType:

Scheme   | Value
=================
Scheme X | 3000.0
Scheme Y | 7000.0

Now, if I try to calculate my loading factor in that query, all goes wrong - i'll demonstrate. Given the following query:

WITH MEMBER [Measures].[Loading Value]
AS
(
   [Measures].[Value] * [Measures].[Loading Factor]
)
SELECT
{
 [Measures].[Value] ,
 [Measures].[Loading Value]
} ON 0,
[Valuation].[Scheme] ON 1
FROM Cube

I get the result

Scheme   | Value  | Loading Value
=================================
Scheme X | 3000.0 | 120.0
Scheme Y | 7000.0 | 280.0

Basically, what is happening is that it is suming my Loading Factor and then multiplying that by the Sum of my values(The first row above should be 1000 * 0.02 + 2000 * 0.02 = 60. Instead it's calculating 3000 * 0.04 = 120).

This is of course a contrived example, my actual structure is a bit more complex - but I think this demonstrates the problem. I was under the impression that the calculated member in the example above should occur on a row-by-row basis, instead of at the end of an aggration of my Value measure.

Thanks for any replies.

Answer 1

Your [Measures].[Loading Factor] - How is that set, is it a SUM?

Calculated members are generally done as per the rows returned if I remember - Unless you specify otherwise.

If you want an example, take a look at the currency conversion wizard output - This does something similar using the LEAVES command - You will need to do this in the MDX script as a SCOPE'd command though.

Given your description, the code could be something like:

CREATE MEMBER [Measures].[Loading Value] AS NULL

Scope( { [Measures].[Loading Value] } );   

    Scope( Leaves([Valuation]) );                                         

            This = [Measures].[Value] * [Measures].[Loading Factor]                                  
            Format_String(This) = "#,##0.00;-#,##0.00";                                                                                

    End Scope;   
End Scope;