Calculating Data in Stories

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 use in a chart or table. In a table, you apply calculations either to the account 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 Measures

    Perform a calculation on one or more members of either the account dimension or the measure. A new calculated member of the selected dimension is created as a result.

  • Restricted Measures

    Restrict the data from a member of either the account dimension or the 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. A new restricted member of the selected dimension is created as a result.

  • Difference From

    Find the difference in an account’s value between two dates. A new calculated account member is created as a result.

  • Currency Conversion

    For models with currency conversion enabled, add a currency type of calculation.

In a chart, calculations based on the account dimension can be added as new measures. The figure below shows the supported calculation types.

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.
  • In stories, calculations can be created on both the account dimension as well as measures.
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. 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.

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.)

Calculated Dimensions

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

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 Calculated 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 Calculated Dimension members will be updated accordingly.

Table Formulas

You can use table formulas to perform on-the-fly, excel-like calculations. The figure below illustrates an example where you calculate percentage of sales. You can use a table formula where cell D5 is locked via $D$5.

The row number & column letters are displayed in the table but they can nevertheless be used in table formulas.

Create Story Calculations

Task Flow

In this 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 slider bar
  • Add a calculated dimension
  • Create a measure-based dimension

Save progress to your learning plan by logging in or creating an account