Summarising Variables
How to combine lines from Variables with Dimensions
Last updated
How to combine lines from Variables with Dimensions
Last updated
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.
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.
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
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.
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.
