🧮 Calculated Members

This article introduces how to create calculated members for a multidimensional cube. In a semantic model, calculated members are user-defined virtual members generated based on existing data and calculation logic. Calculated members allow users to perform custom calculations and aggregation operations in the data model to provide richer and more flexible analysis and insights.

info

In Multidimensional Expressions (MDX), a calculated member is a member resolved by calculating an MDX expression to return a value. The ability to construct and use calculated members in MDX queries provides great manipulation capabilities for multidimensional data.

You can create calculated members at any position in the hierarchy. You can also create calculated members that depend on existing members in the multidimensional cube and other calculated members defined in the same MDX expression.

Creating Calculated Members

Calculated Members use calculation formulas to compute the measure fields or dimension members that need to be queried. Here we take the Sales model created in the Multidimensional Cube as an example to create a calculated member for Profit:

1. In the Calculated Members area of the multidimensional model editing interface, click the add icon to create a new calculated member;
2. Select this calculated member, and enter the following information in the calculated member property editing interface on the right:
   1. Name: Profit
   2. Caption: Profit
   3. Dimension: Measures
   4. Visible: ✅
   5. Formula: [Measures].[Sales] - [Measures].[Cost]
3. Save

The calculation formula used here follows the syntax of Multidimensional Data Expressions (MDX). The expression for calculated members can refer to the expression writing after the AS keyword in MDX Calculated Members.

To verify the effect of the newly created calculated member, you can go to the Calculation Page and perform a query in the preview area. In the row area, select the dimension Time and the level field Year, and in the column area, add three measure fields: Sales, Cost, and Profit. Then click the refresh button in the menu bar, wait for the data to display, and verify the results.

Creating Complex Formulas

In addition to using simple basic mathematical operations, MDX also has rich functions to help data analysis calculate complex business scenarios. Below we create two more complex calculation formulas: Profit Last Period and the Growth Rate relative to the previous period.

MDX functions may vary in implementation on different platforms, so the actual running results should be used as the standard.

Profit Last Period

First, we create a formula to calculate the Profit for the last period. Here, we use an MDX function PrevMember to calculate the member of the previous period, and then combine it with the Profit measure itself to express the formula for the Profit of the previous period: