# MDX calculation has wrong order of precendence

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.

## Answers

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;

I'm not sure I follow your example completely, but you might try using SOLVE_ORDER and SCOPE_ISOLATION to manipulate the order of the calculations.

For example,

WITH MEMBER [Measures].[Custom Calculation] AS '([Measures].[Sales Count] - [Measures].[Unit Returns])', SOLVE_ORDER = 65535, SCOPE_ISOLATION = CUBE SELECT {[Measures].[Custom Calculation]} ON COLUMNS, NON EMPTY [Time].[YQMD].[Day].AllMembers ON ROWS FROM [Waremart]

Thes one turned out ot be REALLY easy.

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