Creating Formulas and Calculations

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Explain calculations created for import models
  • Create calculated measures in a model

Dimension Member Formulas

In account-based models, Dimension Member Formulas are configured in the Account dimension type to perform calculations using existing dimension members, such as Labor as a Percent of Cost (calculated using the Labor and Cost dimension members.

The previous figure an example of a dimension member formula for calculating labor as a percentage of total cost. You can see:

  1. The formula into the Account dimension.
  2. The results in a table in a story.

You can use dimension member formulas to calculate account values that are restricted to other dimension member values such as Prior Year Revenue. A dimension formula is available in any story that uses a model that contains the account dimension with a formula and can be viewed in by displaying the formula bar.

An account dimension formula can be used as it was created in the dimension, or it can be modified in specific models. In this scenario, the model can contain its own version of the dimension formula.

Calculated Measures

Calculated Measures are similar to Dimension Member Formulas, but are created in the Calculations workspace for measure-based models. They do not appear in the Data Foundation view because, like Dimension Member Formulas, the results are not stored in the model. They are calculated only when used in the story.

To create a calculated measure:

  1. Go to the Calculations Workspace.
  2. Add the Calculated Measure.
  3. Create the Formula.
  4. Preview the results.

Calculated Measure Exception Aggregation Settings

Calculated measure exception aggregation settings allow you to incorporate dimensions into the calculated values. For example, if you have a model with Store and Product Line dimensions and a Revenue measure, you may want to create a measure that counts the number of product lines.

With Exception Aggregation, you can specify that you want the Revenue value to be counted by each Product Line.

In the preceding figure, the weekly counts of product lines are counted per store. The monthly amount is not a total (which would be inaccurate) but rather reflects the total number of unique product lines sold in that store for that month.

Exception Aggregation can be quite complex, but may be necessary to ensure that the final result of a calculation is correct. For a more detailed explanation and examples of its use with SAP Analytics Cloud models (and stories), refer to this blog.

Create a Calculated Measure in a Model

Task 1: Add Calculated Measures to the Model

Business Example

You need to add calculated measures to your universe model for price and gross margin percentage.

Task Flow

In this simulation, you will be guided through completing the following task:

  • Create a calculated measure for Gross Margin %

Task 2: Build a Story with Your Universe Model

Business Example

You need to create a story using the calculated measures for price and gross margin percentage.

Task Flow

In this simulation, you will be guided through completing the following tasks:

  • Create a canvas story
  • Configure the story using the universe model

Log in to track your progress & complete quizzes