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

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.

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.

With the new model type, developers can create calculated measures in the model in cases where they need to be shared across stories.

Additionally, 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.

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 preceding figure 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.

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

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

Note
SAP Business Warehouse and SAP BusinessObjects Universe live models do not support dimension type calculations.

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.

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

Create a Dimension Calculation in a Story

Business Scenario

You have created a story and need to add a measure-based dimension to the chart.

Task Flow

In this simulation, we will guide you through performing the following tasks:

  • Create a measure-based dimension
  • Use the Calculation Editor to configured the dimension calculation

Table Formulas

You can use table formulas to perform Microsoft Excel-like calculations. The following example illustrates calculating the percentage of sales. 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.

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

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.

What skills will you develop in this practice exercise?

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