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