Add Calculated Metrics
You can add a calculated metric to a model by writing an MDX formula that operates on existing metrics already defined in the model.
Calculated metric formulas are written in MDX syntax, but only support a small subset of MDX functions and operators. They can only operate on the existing metrics in the model, and must return a numeric value. You can do simple math operations to combine metrics, or simple comparison operations to return a given metric value when certain conditions are met.
Note: AtScale recommends using Dimension Calculation Groups, which allows you to reduce the number of calculated metrics in a model.
Before You Begin
-
If you have experience with creating formulas in Tableau, you need to consider that the formulas for calculated metrics in AtScale are somewhat different. For details, see Calculated metric syntax.
-
The syntax validation mechanism used when entering the MDX formula detects cases where the equals operator is used incorrectly for comparing the CurrentMember function to a scalar value. Also, it displays a warning about using the
[Dimension].[Hierarchy].[Level].CurrentMember
syntax.If you need to disable these syntax checks, you can do so using the
query.language.mdx.currentMember.allowLegacySyntax
engine setting. For details, see Changing Engine Settings.
Procedure
To add a calculated metric:
- In Design Center, open the model you want to add a calculated metric to.
- If you want to reference a particular metric in the calculated metric, determine its unique name.
Note: If you plan to use the CURRENTMEMBER.NAME function in your formula, you will also need to get the dimension attribute's unique name. Pay attention to role-playing dimensions, as their unique names have a prefix that proceeds the attribute's unique name.
-
Click the plus icon at the top of the page and select New Calculation. The Edit Calculated Measure panel opens.
-
Complete the following fields:
- Display Name: The name of the calculated metric as it appears in AtScale.
- Unique Name: The unique name of the calculated metric. This must be unique across all repositories and subrepositories.
- Description: A description of the calculated metric.
- MDX Aggregation Function: The aggregation function to use when this calculated metric is referenced via the Aggregate MDX function.
Note: AtScale recommends setting this field to a value other than None if you plan on referencing this calculated metric from calculation groups. Otherwise, you may encounter errors at query time.
- Visible in BI Tool: Determines whether the calculated metric appears in BI tools.
- Formatting: The format for results of queries against the calculated metric. If you select Custom, you must also enter a custom format.
-
In the Formula field, enter the MDX formula you want to use. Be aware of the following:
- The formula must return a numeric value.
- When using Power BI, AtScale recommends using the hierarchy name in the expression, rather than the dimension name.
- You cannot combine metrics from multiple fact tables in the same formula if using CURRENTMEMBER.NAME in your formula.
- You can only evaluate dimensions in a calculated metric formula with metrics from a single fact table.
-
Click TEST MDX SYNTAX to validate your formula.
-
Click Apply.
The new calculated metric appears in the calculations/
folder in the
Repo Browser and can be added to models as needed.
MDX reference
For details about the MDX functions and operators allowed in a calculated metric formula, see MDX Reference.