Summarising Variables

How to combine lines from Variables with Dimensions

Overview

When referencing a Variable with one or more Dimensions, from a Variable with different Dimensions, we can summarise the target Variable in several different ways.

Summarise method
Details

Summary Line

If the target Variable has a row summary, the value of the row summary is returned

New Summary

Creates a custom summary - you can choose which lines of the target Variable are summed together

Sum based on third Variable

Sums Variable rows together according to a categorisation made in a third Variable

By default, if you create a reference to a Variable which has Dimensions that do not match the Calculation Variable, a Summary Line reference will be created. If a Row Summary is not present in the target Variable, a New Summary adding together all lines of the target Variable will be created.

Referencing the Summary Line

Example Summary Line reference

If the target Variable already has a row summary line, your Calculation can simply reference that summary. This is indicated by an [All] suffix in the Variable reference.

This reference will often be created for you by default. If you need to create the reference yourself, edit the Calculation Part and select the option Reference the SUMMARY line of the Variable in the Reference type step, as shown below.

Creating a Summary Line reference

Creating a New Summary

Example 1

Example New Summary reference

In the above example, we have created a Variable, Platinum Revenues, that sums together the Platinum items of the Revenues by region and membership type Variable.

This is achieved by creating a New Summary reference, via the following steps:

  • Edit the Calculation Part and navigate to the Reference type page, as shown in the screenshot below

  • Select the Create a NEW SUMMARY option (1) - this displays a new Variable summary step in which you can select the desired summary options (2)

  • In this case, we need to select an option for each of the two target Dimensions, Region and Membership tier. As we are adding together the Platinum lines, we select Sum for Region, and Platinum for Membership Tier

Creating a New Summary: Example steps

Example 2

Example New Summary reference

In this example, we have created a Variable, Revenues by region, which has the Region Dimension applied, and we want to sum together all revenues for each region.

To do this, we follow the same steps as in Example 1, but this time we have an option (1) to link the Region Dimension in the target Variable to the Region Dimension in the Calculation Variable.

In this situation, Models would automatically generate this summary when the reference is created, as it can see that the Region Dimension is present in both Variables.

Creating a New Summary: Example steps

Summarising by a third Variable

Example Summary by a third Variable

In the example above, we have seven cost items, which we want to assign to either Capex or Opex and then automatically sum up the totals for the two types of cost.

We can achieve this in a single Variable reference as follows:

  • Edit the Calculation Part and navigate to the Reference type page, as shown in the screenshot below

  • Select the SUM lines together based on a third Variable option (1) - this displays a new Variable selector step in which you can select the assignment Variable (2)

  • In this step, select a Variable with which to perform the assignment. This Variable will need to have the same Dimensions as the referenced Variable. By default, this list will be filtered to Variables with the correct Dimensions.

Create the assignment Variable by using the Validation data type - in this case using Opex/Capex as the Validation Dimension.

Summarising based on a third Variable: Example steps
PreviousLookupsNextPeriod References

Last updated