Calculated Members

Calculated members allow you to define an MDX expression/formula that is attached to a new dimension's member. Note that a calculated member within this document is referring either to a calculated measure (i.e. a new [Measures] being calculated) or a calculated member (i.e. a new dimension member being calculated).

Calculated members are never aggregated and instead are always evaluated according to the current context.

Calculated members may be defined at cube level (they are then available as regular members for every SELECT query) but also within the scope of a given SELECT expression only as follows:

WITH
    MEMBER [Measures].[Market Share] AS ([Measures].[Amount]) / ( [Product].CurrentMember.parent ,[Measures].[Amount] ), FORMAT_STRING = "Percent"
SELECT
    [Product].[icCube].Children ON 0,
    [Measures].[Market Share] ON 1
FROM
    [Sales]

As you can see above, there are three main parts of a calculated member.

Member Identifier: This specifies the name of the calculated member, the dimension to which the calculated member belongs, and its hierarchical positioning. Remember that [Measures] is also a dimension in MDX.

The main part of the definition is the formula which tells us how the results are derived.

Optional properties can provide additional information such as the display format of the values.

When calculated members are defined in the query by using the WITH clause (like the example above), the scope of the calculation is limited to the scope of the query. The MDX engine does not remember the member once the query execution is over. In order to save this new member in your cube, use CREATE MEMBER on the Script of the cube.

With the new calculated measure being defined, each cell (indexed by a tuple containing that calculated measure) is going to be calculated using the MDX formula of the new calculated measure.

But what if the indexing tuple contains more than one calculated member? There has to be a way to define the calculated members' precedence. The SOLVE_ORDER property is available for this purpose; the calculated member with the highest SOLVE_ORDER is going to be evaluated first.

WITH
        MEMBER [Measures].[Profit] AS [Measures].[Amount] - [Measures].[Cost], SOLVE_ORDER = 0
        MEMBER [Time].[Calendar].[Year].[2010/2009] AS [Time].[Calendar].[Year].[2010] / [Time].[Calendar].[Year].[2009], SOLVE_ORDER = 1
SELECT
        [Measures].[Profit] ON COLUMNS,
        [Time].[Calendar].[Year].[2010/2009] ON ROWS
FROM
        [Sales]

The example above is selecting a single cell indexed by ( [Time].[Year].[2010 / 2009], [Measures].[Profit] ). The SOLVE_ORDER property will be used to determine which formula must be applied to get a consistent result. [2010 / 2009] having the highest SOLVE_ORDER, is going to be evaluated as follows: ( [Profit], [2010] ) / ( [Profit], [2009] ).

Note that calculated members can reference other calculated members.

To get more details about calculated members, several illustrated working examples are available from our MDX documentation pages here.

Next chapter: Named Sets.

( Do you want to try this out live? Our Community Edition is available here. )