It is not an uncommon situation to have overlapping or conflicting calculations when you have calculations on both the columns and rows axes. For example, we want to know the growth in [Avg Sales Price] from 2004 to 2005. We can formulate a query that includes two calculated members, one for [Avg Sales Price] in the measures dimension, and one for [YOY Growth] in the Calendar Year dimension, as in this query:

WITH

MEMBER [Measures].[Avg Sales Price] AS '

[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]'

MEMBER [0CALYEAR].[YOY Growth] AS '

[0CALYEAR].[2005]-[0CALYEAR].[2004]'

SELECT

{[Measures].[0D_NETSALES],[Measures].[0D_INV_QTY],[Measures].[Avg Sales Price]} ON COLUMNS,

{ [0CALYEAR].[2004] , [0CALYEAR].[2005] , [0CALYEAR].[YOY Growth] }ON ROWS

FROM [$0D_DX_M01]

As you can see in the above figure, the calculations for [YOY Growth] and [Avg Sales Price] overlap in the bottom right-hand cell. In our query, the calculation for [Avg Sales Price] takes precedence and does not give us year-over-year growth, but rather the result of dividing [Net Sales] by [Billled Quantity] for the [YOY Growth] member. That's not the result we want.

To get the desired answer, we can assign a SOLVE_ORDER to each calculated member so that the [YOY Growth] calculation takes precedence over the [Avg Sales Price] calculation. We do this in the following query:

WITH

MEMBER [Measures].[Avg Sales Price] AS '

[Measures].[0D_NETSALES]/[Measures].[0D_INV_QTY]' SOLVE_ORDER = 0

MEMBER [0CALYEAR].[YOY Growth] AS '

[0CALYEAR].[2005]-[0CALYEAR].[2004]' SOLVE_ORDER = 1

SELECT

{[Measures].[0D_NETSALES],[Measures].[0D_INV_QTY],[Measures].[Avg Sales Price]} ON COLUMNS,

{ [0CALYEAR].[2004] , [0CALYEAR].[2005] , [0CALYEAR].[YOY Growth] }ON ROWS

FROM [$0D_DX_M01]

This gives us the correct result, as shown in the figure below.

The expression with the higher solve order takes precedence over the one with the lower. Note that the SOLVE_ORDER clause is applied after the calculation and is enclosed within single quotes. If you've created a query with multiple calculated members and you are not getting the result you expect, make sure that you've correctly specified the precedence of each calcuation. The default precedence is 0.