Calculating Data in Stories

In a story, the calculation editor allows you to create calculations for a chart or table. For each type of calculation, a new calculated member is created. You can also use dimension attributes as part of a calculation.

In this short video, we take a closer look calculated measures, restricted measures, and measure-based dimensions.

The following calculation types are supported:

• Calculated Measure: Perform a mathematical calculation on one or more members of either the account dimension or the measure.
• Restricted Measure: Restrict the data from a member of the account dimension or a measure so that it includes certain members of one or more dimensions. For the date dimension, you can pick dynamic values, such as year-to-date or previous quarter.
• Difference From: Find the difference in a value between a specific date and time relative to that date.
• Aggregation: Create calculations from aggregations such as sum, count, average, etc. You can also choose what conditions are required for the aggregation to be applied, and when the conditions are required.
• Date Difference: Create a calculation that shows the time interval between two dates, either days, months, or years.
• Dimension to Measure: Convert a string data format to a numeric format.
• Running Total: Create a running calculation in a table or chart based on the dimensions in the widget.
• Currency Conversion: For models with currency conversion enabled, calculate a value based on a currency exchange rate.

In a measure-based model, developers can create calculated measures in the model in cases where they need to be shared across stories.

Additionally, since a measure-based model can contain an account dimension as well as measures, it does not support (or need) cross calculations. Cross calculations are still available for account-based models, but SAP recommends using measure-based models. For this reason, cross calculations are not emphasized in the training.

Create a calculated measure in the Builder panel. When setting up your calculation, you 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.

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.

You can also add simple calculations by selecting two measures in a widget, right-click, choose Add calculation, and choose the calculation you want.

When you add a calculation (or column/row) to a table, you are given the option to add it either as a Repeating or Single column/row. This option is only applicable if you have more than one dimension in a row/column. The table in the following example has only one dimension in the rows, so it does not matter if Repeating or Single is selected.

In the following figure, there is a dimension in the columns, along with the two measures. Choosing Repeating adds the Subtract calculation to every dimension member column.

However, in the following figure, only the dimension member whose column was right-clicked with Single selected has the Subtract calculation.

You can create calculations on dimensions to manipulate non-numeric data; for example, to concatenate dimensions together, group dimensions, etc.

When you are working with a high number of dimension members, it may be useful to group them based on a certain measure. For example, in a sales workflow, you want to group sales managers based on how much revenue they are bringing in. Measure-based Dimensions allow you to easily create a new calculated dimension and define the criteria for the dimension members.

Moreover, as your data gets updated, the Measure-based Dimension members will be updated accordingly.

You can use table formulas to perform cell-based calculations similar to Microsoft Excel syntax. The following example illustrates calculating the percentage of sales by referencing specific cells in the table. You can use a table formula where a cell is locked via $; for example, $D$5.

In the example, cell C2 represents the Forecast Gross Sales for each Product member in the hierarchy. Cell C$4 represents the Forecast Gross Sales for the entire Product Group member; in other words, the total Forecast Gross Sales.

For more information regarding table formulas, see the Formula Bar topic in the SAP Analytics Cloud user guide.