Creating Formulas and Calculations

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

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 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 sold by each store.

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

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.