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.

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. Do one of the following in the Formatting field:

   • Leave the default value for the selected calculation as is.
   • Choose another value 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. Choose 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, choose a value from the list, or 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.

note

Note: The AGGREGATE function does not support all measure aggregation functions. For example, "Distinct Count" and "Average" are not supported. Using the AGGREGATE function with an unsupported Measure function will result in the following error message, "Calculation contains Aggregate over an unsupported automatic reaggregation type". In many cases, this issue can be avoided by using a supported in-line aggregation function, such as AVG, rather than the AGGREGATE function. (ATSCALE-13129)

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

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