SAP Learning Journey

Calculating Data in Stories

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Calculate data in stories using the calculation editor

Calculated Measures

Calculation Editor

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

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 excludes 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 two dates.
Currency Conversion
For models with currency conversion enabled, calculate a value based on a currency exchange rate.
Note
BW/Universe live models do not support dimension type calculations and there are only three options when adding measure-based calculations in the story:
  • Calculated Measure
  • Restricted Measure
  • Aggregation

(Live models based on HANA calculation views have all of the options.)

Note
With the introduction of the new model, there are even more options when it comes to calculations:
  • You can create calculated measures in the model in cases where they need to be shared across stories.
Note
Since the new model can contain an account dimension as well as measures, it does not support (or need) cross calculations. Cross calculations are still available for classic models, but SAP recommends using the new model for all models. For this reason, cross calculations are not emphasized in the training material.

How to Create a Calculated Measure

Create a calculated measure in the Builder pane. 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.

Watch this video to learn how to create calculated measures.

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.

Simple Calculations

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

Repeating vs. Single

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. In the figure Simple Calculations there is only one dimension in the rows, so it does not matter if Repeating or Single is selected.

However, in the figure Repeating vs. Single there is a dimension in the columns, along with the two measures. Choosing Repeating adds the calculation to every dimension member column. Choosing Single adds it only to the dimension member whose columns you right-click.

Calculated Dimensions

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

Watch this video to learn how to create calculated dimensions .

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.

Create Story Calculations

Task 1: Use calculations in a story

Business example

You need to know how to create calculations in stories to enhance the decision making process.

Task flow

In this practice exercise, you will perform the following tasks:

  • Create the GM % of revenue measure
  • Create a restricted measure
  • Create a YTD measure
  • Add a difference from measure
  • Use an aggregation calculation
  • Add a calculated measure with a mark up
  • Add a calculated dimension
  • Create a measure-based dimension
ExerciseStart Exercise

Table formulas

You can use table formulas to perform on-the-fly, excel-like calculations. The figure Example: Table Formula illustrates calculating the percentage of sales. You can use a table formula where a cell is locked via $; for example, $D$5.

Note
The row number and column letters are not displayed in the table, but they can be used in table formulas.

Create Table Formulas

Task 1: Use table formulas in a story

Business example

You are working with your story and decide that you need to add a table-specific calculation.

Task flow

In this practice exercise, you will perform the following tasks:

  • Add a table formula for % of gross sales by product
  • Add a table formula for % variance
ExerciseStart Exercise

Log in to track your progress & complete quizzes