Certain Dimension Members are 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 Time).
To enable these calculations, check the Member Formula checkbox in the target Dimension's settings.
Once Member Formulas are enabled, a new f (x) column with appear and you will be able to define calculations by selecting .
The Member Formula window allows you to define calculations using Members from the same Dimension. To add Members to the calculation, simply double-click the Members in the hierarchy.
The formula supports all basic arithmetic operations and also supports some advanced formula structures 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.
Upon saving, 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.
Tip: Refer to the Example tab for example formulas.
To see all Member Formulas currently in a Dimension, go to the Calculate tab.