Skip to main content

Calculated Member

This article explains how to create calculated members for multidimensional datasets. In semantic models, Calculated Members are user-defined virtual members generated from existing data and calculation logic, enabling custom calculations and aggregations for richer, flexible analysis and insights.

info

In Multidimensional Expressions (MDX), calculated members are resolved by evaluating an MDX expression to return a value. The ability to build and use calculated members in MDX queries provides significant flexibility for manipulating multidimensional data.

You can create calculated members at any point in a hierarchy. You can also create calculated members that depend on existing members in the dataset and other calculated members defined in the same MDX expression.

Creating Calculated Members​

Calculated Members use formulas to compute measure fields or dimension members for queries. Using the Sales model from Multidimensional Datasets as an example, we create a Profit calculated member:

  1. In the multidimensional model editor, click the add icon in the Calculated Members section to create a new calculated member.
  2. Select the calculated member and enter the following in the properties editor:
    1. Name: Profit
    2. Caption: Profit
    3. Dimension: Measures
    4. Visible: ✅
    5. Formula: [Measures].[Sales] - [Measures].[Cost]
  3. Save

The formula uses MDX syntax. Refer to MDX Calculated Members for expression syntax after the AS keyword.

To verify, go to the Calculations page, query in the preview area, select the Time dimension and Year level in the row area, add Sales, Cost, and Profit measures in the column area, click refresh, and validate results.

Creating Complex Formulas​

Beyond basic math operations, MDX offers functions for complex business scenarios. Below, we create two complex formulas: Previous Period Profit and Growth Rate relative to the previous period.

MDX function implementations vary by platform; rely on actual results.

Previous Period Profit​

To calculate Previous Period Profit, use the MDX function PrevMember to get the previous period's member, combined with the Profit measure:

(Measures.[Profit], [Time].PrevMember)

To avoid division errors in subsequent formulas (e.g., growth rate), convert empty results to the current period’s value using CoalesceEmpty:

CoalesceEmpty((Measures.[Profit], [Time].PrevMember), Measures.[Profit])

Save as Profit last Period and verify independently.

To use a calculated member as an intermediate result without displaying it, set the Visible property to false to exclude it from the model’s output measure list.

Growth Rate​

With Current and Previous Period Profit, calculate the Profit Growth Rate:

([Measures].[Profit] - [Measures].[Profit last Period]) / [Measures].[Profit last Period]

Save as Profit Growth.

Verify Profit Growth and other fields, specifying a dimension like Calendar in the row context during queries.