Create Calculated Measures for Tables

In SAP Analytics Cloud, the Calculated Measures calculation creates a new measure (or account) that you can use in your table.

Context

Measures (which are referred to as Accounts in a planning model) are numerical values on which you can use mathematical functions. When setting up your calculation, you’ll apply the typical formula functions, conditions, and operators to the data contained in your model.

Calculated measures allow you to perform mathematical and Boolean operations on your data. For example, you can use a calculated measure to show the effect a sales increase of 20% would have on profits.

Note

The number format chosen in Start of the navigation path

Profile Settings Next navigation step

User Preferences End of the navigation path

influences the expected input format for Story Calculated Measures and Calculated Dimensions:

Choosing a number format that uses periods (.) as decimal separators means that commas (,) must be used to separate function parameters (for example, IF(Condition, ValueIfConditionIsTrue, ValueIfConditionIsFalse)).
Choosing a number format that uses commas (,) as decimal separators means that semi-colons (;) must be used to separate function parameters (for example, IF(Condition; ValueIfConditionIsTrue; ValueIfConditionIsFalse)).

If the formula is typed in from scratch, the correct function auto-completion happens based on the user preferences. However, if you copy and paste a full formula string, auto-complete won't be able to adapt if there is a mismatch between separators used and the user preferences.

The following functions, conditions, and operators are available for creating calculated measures. For descriptions of each option, see All Formulas and Calculations.

Functions

`IF()`	`POWER()`	`NOT()`
`ABS()`	`GrandTotal()`	`TRIM()`
`LOG()`	`%GrandTotal()`	`FINDINDEX()`
`LOG10()`	`LENGTH()`	`RIGHT()`
`INT()`	`LIKE()`	`LEFT()`
`FLOAT()`	`SUBSTRING()`	`SPLIT()`
`DOUBLE()`	`ISNULL()`	`ENDSWITH()`

Note

The ISNULL function identifies NULL values, but won't replace a NULL value with a value.

For example, your calculated dimension has the following formula: CD1=IF(ISNULL(D1), "No Value", D1)

The value in cell D1 will not be changed to show the words “No Value”: it will stay as a NULL value.

Conditions:

AND	OR
> (greater than)	< (less than)
>= (greater than or equal)	<= (less than or equal)
= (equal)	!= (not equal)

Operators:

+ (addition)
- (subtraction)
* (multiplication)
/ (division)

Tip

In the Calculation Editor, you only see the functions that are valid for your data source.

Procedure

Select a table and then open the Calculation Editor:
1. Under Rows or Columns in the Builder tab, select Add Measures/Dimensions and add either the Account or Cross Calculations dimension.
  
  Tip
  You can rename the Cross Calculations dimension.
2. For the row or column that you added in the Builder tab, select Add Calculation.
Note
The option to create a new calculation may not appear if calculations are not possible for the table, or model.

The Calculation Editor appears.
Select Calculated Measure from the list.
Enter a name for the calculated measure.
Enter a formula in the Edit Formula area.
Tip

As you are adding formula details, you will see a message appear and disappear: it appears when your formula is not valid and disappears when the formula is valid.

Existing input controls appear in the Available Objects list, and can be added to a formula.

Note
Only single value numeric input controls can be used in formulas.

You can add preset functions, conditions, and operators, by selecting options in the Formula Functions list. You can use IF conditional functions, and you can display a list of possible formulas for the function by pressing Ctrl + Space bar.
The formula editor supports auto-complete and the following shortcuts:
- [ – Returns all relevant measures:
  - Adding a calculation to the Account dimension: shows Account dimension members.
    Note
    The Cross Calculations dimension cannot be used in the Account dimension calculations.
  - Adding a calculation to the Cross Calculations dimension: shows Cross Calculations dimension members.
    Note
    The Account dimension cannot be used in Cross Calculations dimension calculations.
- # – Returns all calculations (that is, measures created using the Calculation Editor).
- @ – Returns input controls. (Only single value numeric input controls are returned.)
Tip

In the list of measures, you see both the measure ID and the measure description. After you select a measure, the formula editor area shows only the measure ID. To view the measure description, click outside the formula editor area.
(Optional) If you want to verify that your formula is formatted correctly, select Format: it may reformat your formula before displaying a valid formula message.
Select OK.

Results

A measure is created based on the formula you entered, and it is added as a new member of the Account or Cross Calculations dimension.

If the formula uses an input control, it will appear on the canvas.

On the canvas, input controls are indicated by the (Formula) icon. If you hover over the icon, all calculations associated with the input control are displayed. By default, the input control is displayed in token mode where input values can be selected from a drop-down list. The input control can be expanded into widget mode, where radio buttons appear beside each value.