Calculated measures allows for defining new measures.
Description
A powerful feature of MDX is defining new measures, known as calculated measures (or calculated members).
They are defined before the SELECT statement :
WITH
MEMBER [Measures].[Diff with United States] AS '[Measures].[Amount] - ([Geography].[United States],[Measures].[Amount])'
MEMBER [Geography].[Geo].[Europe I] AS '[Geography].[Geo].[Europe] - [Geography].[Geo].[Switzerland]'
SELECT
...
In this example, the new measure allows for defining a value based on a formula. This formula may override any member from any hierarchy as defined by the evaluation context of the formula (e.g., axes, slicer, etc...).
Similarly, calculated members can be declared (and removed) at schema level using the CREATE / DROP statements.
DROP MEMBER [Measures].[Benefit]
CREATE CALCULATED MEMBER [Measures].[Benefit] AS [Measures].[Sales] - [Measures].[Cost]
Cell Properties allows for defining meta-information attached to calculated members.
Please, check working examples for further explanation.
See Also
Cell Properties Create (Member) Drop (Member) AddCalculatedMembers StripCalculatedMembers
Examples
A simple example defining a new measure (i.e., [New Value]) as a constant equal to 2.
Query
WITH
MEMBER [Measures].[New Value] AS 2
SELECT
{ [Geography].[Geo].[United States] } ON 0,
{ [Measures].[New Value] } ON 1
FROM
[Sales]
Result
|
United States |
| New Value |
2 |
A classical example showing how to calculate market shares.
Market Share X = Licenses sold of type X / Total number of licences sold
In the example, you can see how to combine in the same expression tuples with different dimensionality.
Query
WITH
MEMBER [Measures].[Market Share] AS ([Measures].[Amount]) / ( [Product].currentMember.parent ,[Measures].[Amount] ), FORMAT_STRING = "Percent"
SELECT
[Measures].[Market Share] ON 0,
[Product].[icCube].children ON 1
FROM
[Sales]
WHERE
[Geography].[Geo].[Europe]
Result
|
Market Share |
| Corporate |
56.47% |
| Partnership |
37.65% |
| Personal |
5.88% |
| Startup |
|
In this example, we show how to overwrite the current member in a calculated member.
Even though we are requesting the value for [United States] on the axis 0, the calculated member is using the value for [Switzerland] as the hierarchy member is redefined for the current tuple (in this example coming from a cell evaluation).
Query
WITH
MEMBER [Measures].[Switzerland Value] AS ( [Geography].[Geo].[Switzerland],[Measures].[Amount] )
SELECT
{ [Geography].[Geo].[Switzerland],[Geography].[Geo].[United States] } ON 0,
[Measures].[Switzerland Value] ON 1
FROM
[Sales]
Result
|
Switzerland |
United States |
| Switzerland Value |
248 |
248 |
Assertion : Cell Equals
SELECT
{ [Geography].[Geo].[Switzerland],[Geography].[Geo].[Switzerland] } ON 0,
[Measures].[Amount] ON 1
FROM
[Sales]
We can ask for the current member of a hierarchy and act in consequence.
In this example we define a measure which is changing the value only for [Switzerland].
Query
WITH
MEMBER [Measures].[Switzerland as 2] AS IIF([Geography].[Geo].currentMember is [Geography].[Geo].[Switzerland], 2, [Measures].[Amount] )
SELECT
{ [Geography].[Geo].[Switzerland], [Geography].[Geo].[United States] } ON 0,
[Measures].[Switzerland as 2] ON 1
FROM
[Sales]
Result
|
Switzerland |
United States |
| Switzerland as 2 |
2 |
768 |
Assertion : Cell Equals
WITH
MEMBER [Measures].[2] AS 2
SELECT
{ [Measures].[2], [Measures].[Amount] } ON 0,
[Geography].[Geo].[United States] ON 1
FROM
[Sales]
Calculated measures are not limited to the [Measures] dimension. New members (hence the name calculated members) can be added into any existing hierarchy.
It's important to note that as for [Measures] the calculated members are not added into the original hierarchy structure. This implies that functions like Children() and Members() will not return calculated members.
Query
WITH
// Calculates the average over [Europe] countries, new member is added without level and parent
MEMBER [Geography].[Geo].[Europe Avg] AS Avg( [Geography].[Geo].[Europe].children )
// Calculates the max for all [Europe] countries, new member added as a child of a [Europe]
MEMBER [Geography].[Geo].[Europe].[Europe Max] AS Max( [Geography].[Geo].[Europe].children)
// Difference between [Europe] and [Switzerland], new member added as a child of a country
MEMBER [Geography].[Geo].[Europe].[Europe - Switzerland] AS [Geography].[Geo].[Europe] - [Geography].[Geo].[Switzerland]
SELECT
{ [Geography].[Geo].[Europe], [Geography].[Geo].[Europe Avg], [Geography].[Geo].[Europe Max], [Geography].[Geo].[Europe - Switzerland],[Geography].[Geo].[Europe].children} ON 0,
{ [Measures].[Amount] } ON 1
FROM
[Sales]
Result
|
Europe |
Europe Avg |
Europe Max |
Europe - Switzerland |
France |
Spain |
Switzerland |
| Amount |
255 |
85.0 |
248 |
7 |
4 |
3 |
248 |
Assertion : Cell Equals
WITH
MEMBER [Geography].[Geo].[Europe Avg] AS Avg( [Geography].[Geo].[Europe].children )
MEMBER [Geography].[Geo].[Europe Max] AS Max( [Geography].[Geo].[Europe].children)
MEMBER [Geography].[Geo].[Europe - Switzerland] AS [Geography].[Geo].[Europe] - [Geography].[Geo].[Switzerland]
SELECT
{ [Geography].[Geo].[Europe], [Geography].[Geo].[Europe Avg], [Geography].[Geo].[Europe Max], [Geography].[Geo].[Europe - Switzerland],[Geography].[Geo].[Europe].children} ON 0,
{ [Measures].[Amount] } ON 1
FROM
[Sales]
The calculated member/measure being evaluated is the .CurrentMember().
Query
WITH
MEMBER [Measures].[Y] AS [Measures].CurrentMember.NAME
MEMBER [Measures].[X] AS [Measures].CurrentMember.NAME + '-' + [Measures].[Y]
SELECT
[Measures].[X] ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Measures].[X] AS "X-Y"
SELECT
[Measures].[X] ON 0
FROM
[Sales]
Overwriting an existing member is not possible. However it's possible to add a member with the same name in a different path.
In this example we are adding [Switzerland] in the root of the [Geo] hierarchy.
Query
WITH
MEMBER [Geography].[Geo].[Swiss-Lausanne] AS ([Geography].[Geo].[Switzerland] /* calculated member */)
MEMBER [Geography].[Geo].[Swiss] AS ([Geography].[Geo].[Europe].[Switzerland] /* hierarchy member */ )
MEMBER [Geography].[Geo].[Switzerland] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland],[Geography].[Geo].[Swiss], [Geography].[Geo].[Swiss-Lausanne] } ON 0
FROM
[Sales]
Result
| Switzerland |
Swiss |
Swiss-Lausanne |
| 56 |
248 |
56 |
Assertion : Cell Equals
SELECT
{ [Geography].[Geo].[Lausanne],[Geography].[Geo].[Europe].[Switzerland], [Geography].[Geo].[Lausanne] } ON 0
FROM
[Sales]
Overwriting an existing member is not possible (if same path).
Query
WITH
MEMBER [Geography].[Geo].[Europe].[Switzerland] AS ([Geography].[Geo].[Lausanne])
SELECT
{ [Geography].[Geo].[Switzerland] } ON 0
FROM
[Sales]
Result
Within the calculated measure the tuple is completed with hierarchies coming from the axis.
In our example, the [Economy] member coming from axis 1 is 'set' in the calculated member.
Query
WITH
MEMBER [Measures].[xx] AS ( [Geography].[Geo].[France], [Measures].[Amount] )
SELECT
[Measures].[xx] on 0,
{ [Geography].[Economy].[France], [Geography].[Economy].[Spain] } on 1
FROM
[Sales]
Result
Assertion : Cell Equals
WITH
MEMBER [Measures].[xx] AS ( [Geography].[Geo].[France], [Geography].[Economy].currentMember, [Measures].[Amount] )
SELECT
[Measures].[xx] on 0,
{ [Geography].[Economy].[France], [Geography].[Economy].[Spain] } on 1
FROM
[Sales]
Within the calculated measure the [Geo] hierarchy is completed by the current context.
In our example, the calculated member tuple ([Geography].[Geo].[France],[Measures].[Amount]) is completed with the member [Geography].[Economy].[Spain] coming from the axis.
Query
WITH
MEMBER [Measures].[xx] AS Iif( ( [Geography].[Geo].[France], [Measures].[Amount] ) = 4 , 'exist', 'no' )
SELECT
Iif( Generate( { [Geography].[Economy].[Spain] }, [Measures].[xx] ) = 'exist', [Time].[Calendar].[2010], [Time].[Calendar].[2011] ) on 0
FROM
[Sales]
Result
Assertion : MDX Equals
SELECT [Time].[Calendar].[2011] ON 0 FROM [Sales]
The calculated measures being evaluated is the .CurrentMember() so beware of infinite loop.
Here [X] as current member of the [Product].[Prod] hierarchy in its formula is creating an infinite loop.
Why is it a recursion ?
( [Product].[Prod].[X] )
is evaluated as ( [Measures].[Amount] )
is evaluated as ( [Measures].[Amount], [Product].[Prod].currentMember )
is evaluated as ( [Measures].[Amount], [Product].[Prod].[X] )
is evaluated as ( [Product].[Prod].[X] )
the latest one defines a recursion.
Query
WITH
MEMBER [Product].[Prod].[X] AS [Measures].[Amount]
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Prod].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
The calculated measures being evaluated is the .CurrentMember() so beware of infinite loop.
Here [X] as current member of the [Product].[Prod] hierarchy in its formula is creating an infinite loop.
Query
WITH
MEMBER [Product].[Prod].[X] AS Avg( [Geography].[Geo].Members )
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Prod].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
A calculated member defined outside any hierarchy is implicitly defined within the [Measures] dimension.
Query
WITH
MEMBER [the-answer] AS 42
MEMBER [the-answer-hierarchy] AS [the-answer].Hierarchy.Name
SELECT
{ [the-answer], [the-answer-hierarchy] } ON 0
FROM
[Sales]
Result
| the-answer |
the-answer-hierarchy |
| 42 |
Measures |
Assertion : MDX Equals
WITH
MEMBER [Measures].[the-answer] AS 42
MEMBER [Measures].[the-answer-hierarchy] AS "Measures"
SELECT
{ [Measures].[the-answer], [Measures].[the-answer-hierarchy] } ON 0
FROM
[Sales]
A calculated member can be defined directly into a level of depth zero.
Query
WITH MEMBER [Product].[Prod].[Company].[New Company] AS 42
SELECT FROM [Sales] WHERE [Product].[Prod].[Company].[New Company]
Result
Assertion : Cell Equals
WITH MEMBER [Expected Result] AS 42
SELECT FROM [Sales] WHERE [Expected Result]
A calculated member cannot be defined into a level of depth greater than zero without
specifying any parent in the previous level.
Query
WITH MEMBER [Product].[Prod].[Article].[OEM] AS 42
SELECT FROM [Sales] WHERE [Product].[Prod].[Article].[OEM]
Result
Error Code : OLAP_CALC_MEMBER_ORPHAN
Error Message : cannot attach the calculated member 'OEM' into the level '[Product].[Prod].[Article]' without defining any parent (hint: attach the calculated member to a member of the previous level)
Recursion is supported but beware of infinite loop.
Query
WITH
MEMBER [Product].[Prod].[X] AS [Product].[Prod].[X]
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Prod].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
Recursion is supported but beware of infinite loop.
Query
WITH
MEMBER [Product].[Prod].[X] AS 1 + [Product].[Prod].[X]
SELECT
[Product].[Prod].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Product].[Prod].[Company].[X] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( X)
Recursion is supported but beware of infinite loop.
Query
WITH
MEMBER [Measures].[Z] AS [Measures].[X]
MEMBER [Measures].[Y] AS [Measures].[Z]
MEMBER [Measures].[X] AS [Measures].[Y]
SELECT
[Measures].[X] ON 0
FROM
[Sales]
Result
| X |
| #ERROR : (OLAP_CALC_MEMBER_STACK_OVERFLOW) |
Error Code : OLAP_CALC_MEMBER_STACK_OVERFLOW
Error Message : Calculated member [Measures].[Y] stack overflow (see icCube.calcMemberMaxEvaluationFrameStackSize XMLA property) detected at ( Y)
XMLA Properties
Find here the value of the XMLA properties when not specified otherwise.
icCube OLAP version: 3.0.3 03-Mar-2013 14:06:07 GMT