Skip to main content

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

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 global setting. For details, see Configuring Global Settings.

Procedure​

To add a calculated metric:

  1. In Design Center, open the model you want to add a calculated metric to.
  2. If you want to reference a particular metric in the calculated metric, determine its unique name.
note

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.

  1. Click the plus icon at the top of the page and select New Calculation. The Edit Calculated Measure panel opens.

  2. 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

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.

  1. 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.

  2. Click TEST MDX SYNTAX to validate your formula.

  3. 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.