Creating derived metrics in hierarchy reports using MDX expressions

You can create derived metrics in hierarchy reports using MDX expressions.

1. Open the dossier you want to modify.

2. Create a derived metric and reserve it for future use in a visualization. In the Datasets panel, click More to the right of the dataset in which you are creating a derived metric and choose Create Metric.

3. Enter a name for the metric in the Metric Name field.

4. Enter the derived metric definition in the right pane using the ApplySimple function. This function allows you to enter an MDX expression to create a derived metric. You can type arithmetic operators (such as + and -) and values directly in the metric expression.

5. The Apply Simple function does not appear as a selection in the Functions pane. You must enter it manually. In addition, you must add a dummy metric to the end of the function as shown in the example below. This function is not used in the definition. However, it is necessary since all metrics require two arguments for validation.

   Create a derived metric for the gross profit margin.

   ApplySimple("[Measures].[Gross Profit] / [Measures].[Sales Amount]", [Sales Amount])

   Use Objects panes to assist in the creation your derived metric definition. Locate the objects you want to add by using the Search field at the top of the pane. Then, double-click objects to add them to the Formula pane.

6. If you need to delete the metric formula and begin again, click Clear.

7. Click Validate to check if the metric expression is valid.

8. Click Save.