Calculating Data in Stories

Objective

After completing this lesson, you will be able to Calculate data in stories using the calculation editor.

Create Calculated Measures

Calculation Editor

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.

Calculating Data in Stories

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

Supported Calculation Types

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.

Note

Not all data sources for models support all calculation types.

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.

How to Create a Calculated Measure

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.

Simple Calculations

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

Repeating Versus 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. The table in the following example has only one dimension in the rows, so it does not matter if Repeating or Single is selected.

Simple calculations from the context menu

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.

Table with Repeating calculation column added

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

Table with Single calculation column added

Create Measure Calculations in a Story

Business Scenario

You have created a story and need to create a variety of measure calculations.

Task Flow

In these simulations, we will guide you through the steps to 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

Task 1: Create the GM% of Revenue Measure

Task 2: Create a Restricted Measure and Year-to-Date Measure

Task 3: Add a Difference From Measure

Task 4: Use an Aggregation Calculation

Create Calculated Dimensions

Calculated Dimensions

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

Using calculated dimensions in an SAP Analytics Cloud story. Create Calculated Dimension is selected and the Calculation Editor is open.

Note

SAP Business Warehouse and SAP BusinessObjects Universe live models do not support dimension type calculations.
Example of a calculated dimension. Story on the left, calculation editor with formula on right.

Measure-Based 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.

Measure based dimension definition and result

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

Create Dimension Calculations in a Story

Task 1: Add a Calculated Dimension to a Story

Business Scenario

You have created a story and need to add a calculated dimension to display two dimensions, Entity and Product in one column.

In this practice exercise, you will:

  • Add a calculated dimension to your story
  • Use the Calculation Editor to configure the dimension calculation

Task 2: Create a Measure-Based Dimension

Business Scenario

You have created a story and want to add a measure-based dimension for profit level.

In this practice exercise, you will:

  • Add a calculated dimension to your story and change the Type to Measure-Based Dimension
  • Use the Calculation Editor to configure the measure-based dimension calculation

Table Formulas

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.

SAP Analytics Cloud table with formula create similar to Microsoft Excel

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.

Note

The row number and column letters are not displayed in the table by default but can be activated for easier cell identification.

Additional Information

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

Create Table Formulas in a Story

Business Example

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

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

Log in to track your progress & complete quizzes