Skip to main content
Version: Next

Adding a Calculation

When creating a calculation you can choose one of the available templates, or define a custom expression.

Prerequisites​

  • You have accessed the Calculation Groups panel in the dimension editor, and created or selected the group to which you want to add a single calculation. For details, see Adding Calculation Groups.
  • If you plan on referencing a calculated measure via the Aggregate MDX function in your calculation, ensure that the calculated measure has an aggregation function set. Otherwise, you may encounter errors at query time. For more information, see Add Calculated Measures.

Creating standard calculations​

  1. Open the menu next to the filter field in the panel, and choose New Calculation.

  2. In the Create Calculation dialog, enter a name that is unique within the calculation group. Consider the following:

    • If you are going to use a standard calculation template (see next step) - and you leave the name field empty - the name would be set automatically based on the calculation and dimension attribute you select.
    • When using Tableau or Power BI, consider the limitation described in Known Issues and Limitations.

  3. In the Calculation field, choose one of the available calculation templates (not Custom).

  4. In the Formatting field, do one of the following:

    • Leave the default formatting values for the selected calculation template.
    • Select or deselect the Use Input Measure's Formatting checkbox. When this checkbox is selected, the calculation results always use the formatting defined for the input measure. This is useful for calculations that can't have a standard output format. Note that you cannot choose a formatting option if this checkbox is selected.
    • Choose a formatting option from the list.
    • Choose Custom and enter the format manually in the field on the right.

  5. In the Dimension Attribute field, select hierarchy or attribute from the dimension.

    Consider that when the calculation template requires a time unit, then you cannot select dimensional attributes without time units. For example, if you select the "Year over Year" calculation, then the dimension attribute list will show all dimension attributes; however, you will only be able to select levels or level aliases that have non-null Time-Unit settings.

  6. Review the expression in the bottom part of the dialog.

    In case you want to modify the expression manually you can choose the Advanced editing button. Note that this would make the calculation custom; for details, see the section below.

  7. Click Save.

    The system would validate the expression. When it is valid the calculation would be saved. Otherwise, it would display messages with information about the issues.

Creating custom calculations​

  1. Open the menu next to the filter field in the panel, and choose New Calculation.

  2. In the Create Calculation dialog, enter a name that is unique within the calculation group.

  3. In the Calculation field, choose Custom.

  4. In the Formatting field, do one of the following:

    • Leave the default formatting values for the selected calculation template.
    • Select or deselect the Use Input Measure's Formatting checkbox. When this checkbox is selected, the calculation results always use the formatting defined for the input measure. This is useful for calculations that can't have a standard output format. Note that you cannot choose a formatting option if this checkbox is selected.
    • Choose a formatting option from the list.
    • Choose Custom and enter the format manually in the field on the right.

  5. Enter the expression you need.

    You can use the Hierarchies to add dimension hierarchies, levels, or attributes, and the Functions menus to add MDX functions to the expression.

  6. Review the expression in the bottom part of the dialog.

  7. Click Save.

    The system would validate the expression. When it is valid the calculation would be saved. Otherwise, it would display messages with information about the issues.

Result​

The calculation you created would be displayed in the Calculation Groups panel. If needed, you can modify or remove it.