Certain Dimensions Members are sometimes defined by a specific, static relationship with other members or values. For example, a variance scenario like Actual v Forecast might be defined as:
[Actual v Forecast] = [Actual] - [Forecast]
For situations like these, you can define an MDX Calculation directly on the member as a Member Formula. You can define Member Formulas on any Dimension except for Time. To enable these calculations, check the Member Formula checkbox in the Dimension settings.
Once Member Formulas are enabled, a new f (x) column with appear and you will be able to define calculations by clicking on the gear icon .
The Member Formula window allows you to define calculations using members from the same dimension. To add members to the calculation, simply double-click on the members in the hierarchy.
The formula supports all basic arithmetic operations and also supports some advanced formula structure such as case statement. After you have constructed the formula, you can format it by selecting an option from the Format drop-down.
Note: SOLVE_ORDER affects the order in which this MDX statement is applied in the cube. By default, Member Formulas are applied before Model MDX Rules, which are applied before the MDX on a Form.
On save, the formula will be translated to MDX and applied as a Custom Roll-up Formula in the OLAP database. However, if you would like to enter your own custom MDX, you can do so with the NATIVE MDX tab. Please refer to the EXAMPLE tab for example formulas.
To see all member formulas currently in a Dimension, open the CALCULATION tab.