_Mark_99_


I can define a new measure "[Measures].[Weighted Units]" and set its Measure Expression to:

[Measures].[Units] * [Measures].[Allocation]

this gives me the correct results in Cube browser. Note that [Measures].[Allocation] represents a percentage, by which we scale down the Units based on selection in other dimensions.

When i try to do the same through MDX script:

CALCULATE;

({[Measures].[Weighted Units]}, Leaves()) = [Measures].[Units] * [Measures].[Allocation];

i'm not getting the right results: the multiplication does not seem to get applied and "Weighted Units" shows the same values as "Units".

I would need to use MDX script because of the limitations of Measure Expressions (ie. i will need to define other measures based on "[Measures].[Weighted Units]").

Any idea why the MDX script does not work




Re: Problem with MDX calculation

Bryan C. Smith


I'm probably exposing my ignorance here, but could you simply create a calculated measure, [Measures].[Weighted Units], with the formula described above

Bryan







Re: Problem with MDX calculation

_Mark_99_

I've tried with a calculated measure with Expression like

[Measures].[Units] * [Measures].[Allocation]

but the problem is that when it performs the Totals at various levels it's summing up Units and multiplying this by the sum of Allocations. I do not want this as it does not make sense to sum different Allocations together. Think of Allocation as a Price. The multiplication Units * Allocation should always be performed first at leaves levels then these calculated values should be summed up together for the different aggregations.

Pls note that Units and Allocation belong to different Measure Groups (Fact tables).

I've tried with an MDX script like:

CALCULATE;

({[Measures].[Weighted Units]}) = [Measures].[Units] * [Measures].[Allocation];

that is without Leaves() and it's producing the same wrong results as with the calculated measure.

The only way i can get the right results is through the use of Measure Expression but i cannot use this as i need to create another measure ([Weighted Units USD]) with a calculation like [Measures].[Weighted Units] * [Measures].[XRate USD] and apparently you cannot nest measures using Measure Expressions one inside the other

So it seems to me that the best route is the MDX script, if only i could get it working with the Leaves() function.

Any suggestion is greatly appreciated.