Calculated measure wrong sub-totals problem

Hi All,

The problem I encounter at the moment is when I have a calculated measure the sub totals for that measure are coming out incorrect.

Result = (Level - Level2) * Variable

The subtotal of Result should add up all of the green figures below. But instead uses the subtotals of the variables in the calculation (Level, Level2, Variable) shown in red.

2Y 3Y 4Y Subtotal Cust1 Level2 84.76830512 124.8835438 166.9365885 118.6772725 Cust1 Level 80.27276365 123.5586759 168.3462802 116.7156413 Cust1 Variable 60291.16446 190591.6158 195106.6208 445989.401 Cust1 Count 6647 5400 3976 16023 Cust1 Result -271041.4302 -252508.7041 275040.177 -874866.7178

Does anyone know how to get around this.

Thanx

Jabba

Hello! I am not sure about if you use AS2000 or SSAS2005?

Also, are you using a client or are you doing MDX Selects in Management Studio(SSAS2005)?

I suspect that your problem is related to solve order. Result is probably calculated after the Subtotal column. You must find the solve order of the subtotal calculation and change the Result calculation to something lower than that.

You can change this in some clients like ProClarity Professional or Management Studio because you can change the MDX sent from the client.

HTH

Thomas Ivarsson

|||

Hi Jabba,

From your description, Result = (Level - Level2) * Variable is a calculated, not a cube, measure. Calculated measures don't aggregate/roll up according to pre-defined aggregate functions (like Sum, Count, etc). So, if you want Result to sum along a dimension: 2Y, 3Y, 4Y, .., with a hierarchy like Y.Y, you could define Result as:

Code Snippet

Sum(existing [Y].[Y].[Y], (Level - Level2) * Variable)

Alternatively, if Result should roll up along all dimensions from the leaf level, you could try a regular cube measure with a measure expression.

|||

Hi Guys,

Thanks for your replies. I'm using SSAS 2005. Previously I have tried the calculation in both BIDS(Management Studio) and Pro-Clarity and neither works.

I will try to implement both of your suggestions to see what works.

Again, Thank You

|||

Hi Thomas,

I have tried to implement solve order. From my understanding the highest solve order takes precedence.

CREATE MEMBER CURRENTCUBE.[MEASURES].Level
AS [Measures].[LH]/[Measures].[Count],
FORMAT_STRING = "#,##0;(#,##0)", SOLVEORDER = 3,
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].Level2
AS [Measures].[SSH]/[Measures].[Count],
FORMAT_STRING = "#,##0;(#,##0)", SOLVEORDER = 2,
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].Result
AS ([Measures].[Level] - [Measures].[Level2]) * [Measures].[Variable],
FORMAT_STRING = "#,##0;(#,##0)", SOLVEORDER = 1,
VISIBLE = 1;

So Level and Level 2 are given higher solve orders so they are calculated before Result, which uses these two variables.

I have implemented this and it doesnt work.

Jabba

|||

Hey Guys I have just figured out how to get my full MDX from Pro Clarity, the results are still coming out wrong, but would you maybe assist on the solve_order with the code i have given you. The solve order in the code below appeared automatically.

WITH MEMBER [PROD].[PROD].[All].[ Subtotal] AS ' AGGREGATE( EXISTING { [PROD].[PROD].&[4.], [PROD].[PROD].&[5.], [PROD].[PROD].&[6.] }) ', SOLVE_ORDER = 1000 SELECT { { [PROD].[PROD].&[4.], [PROD].[PROD].&[5.], [PROD].[PROD].&[6.] }, { [PROD].[PROD].[All].[ Subtotal] } } ON COLUMNS ,

{ { { [Region].[Region Hierarchy].[Region].&[Sussex] } * { [Measures].[Count], [Measures].[Level], [Measures].[Level2], [Measures].[Variable],[Measures].[Result] } } } ON ROWS

FROM [BPMNewCube]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

Thanx

Jabba

|||

But, in the above MDX query, what is the "Result", which you mentioned was wrong?

|||

Hi Deepak,

I have changed the code above you are right I didnt place in the "RESULT".

The problem still exists: Level2, Level and Result are calculated measures IN BIDS. Level and Level2 are used in the calculation to create result.

Result = (Level - Level2) * Variable

I have tried the methods suggested above, here is the script view in BIDS of my calculated measures:

1)

CREATE MEMBER CURRENTCUBE.[MEASURES].Result

AS SUM({

existing([PROD].[PROD].members)},

([Measures].[Level] - [Measures].[Level2]) * [Measures].[Variable]),

FORMAT_STRING = "#,##0;(#,##0)",

VISIBLE = 1;

2)

CREATE MEMBER CURRENTCUBE.[MEASURES].Level

AS [Measures].[LH]/[Measures].[Count],

FORMAT_STRING = "#,##0;(#,##0)", SOLVE_ORDER = 3,

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].Level2

AS [Measures].[SSH]/[Measures].[Count],

FORMAT_STRING = "#,##0;(#,##0)", SOLVE_ORDER = 2,

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].Result

AS ([Measures].[Level] - [Measures].[Level2]) * [Measures].[Variable],

FORMAT_STRING = "#,##0;(#,##0)", SOLVE_ORDER = 1,

VISIBLE = 1;

Thanx

Jabba