In Metric Trees, you can combine metrics from different data sources—whether they're connected to Cascade through an integration or manually created—and aggregate their data using a calculation. Currently, only sum and average are supported.
The new calculated metric becomes the parent metric, showing the combined results from its child metrics. Once created, this parent metric can be connected to a measure to create a sub-measure hierarchy in your plan.
For example, if you want to see the total sales revenue from AMER, EMEA, and APAC regions, you can create a calculated metric called "Total Sales Revenue" and use 'sum of'. This metric would add together the sales figures from all three regions to give you a combined total.
Create a Calculated Metric Tree
Click Metric trees tab then click + Create new metric and select From calculation.
Select Calculated to create an aggregated metric. This will be your parent metric, and you’ll choose either sum or average as the calculation method.
Next, add your child metrics:
-
To create a new one, select Manual, then click Confirm.
-
To use an existing connected metric, simply drag it into the Metric Tree space from the left side bar.
Before you begin, it's important to note:
-
The Metrics which are needed for calculating the new metric, must already be imported or created manually (can be created as you build the metric tree)
-
You need to have at least 2 metrics for the calculation to work.
-
Depending on how you've chosen Show total as during the import, the metrics will be calculated. i.e., if the display is chosen as "sum of values", then when you do an addition, it'll add up all the values in that metrics. If "last value" is chosen, then the addition will add up only the last displayed values. And, the average calculated metric will either have the average of all the summed up values in case of "sum of values" option, or average of only the last displayed values in case of "last displayed value" option.
-
Once you choose a metric, the metrics list will automatically display only those metrics that've the same units to be chosen for calculation.
Let's understand the steps and workflow with an example: We've imported revenue data from AMER, EMEA, APAC, and now wish to understand the total revenue or the average revenue.
-
In the Name new metric modal, give the new aggregate metric a relevant name. Select the Unit and Trend of its progression (higher the better or lower the better).
-
To suit the example, we'll call the new aggregate metric "Total revenue" for adding up the values, and "Consolidated revenue" for calculating the average.
-
-
Drag and drop the relevant metrics that need to be chosen for calculation to the right pane. Note that, you can choose only those metrics that've the same units for calculations. Click on the metric to edit or delete them.
-
For this example, drag and drop the Revenue from AMER, EMEA, and APAC metrics into the right side pane.
-
-
Drag the connector to connect the chosen metric to the new aggregate metric. Then, select the type of calculation - Sum or Average by clicking on the relevant options. Hover the mouse over the metric, and click the Trash icon to unlink the metric from the calculation or
(Sidebar) icon to view the context of the metric from its sidebar.-
Connect the AMER, EMEA, and APAC revenue metrics tiles to the "Total revenue" metric tile and select Sum, or connect them to the "Consolidated revenue" metric tile, and select Average.
-
-
Click Undo if you wish to revert the latest action or if everything is right, click Preview to see the metric tree in aggregation and click Save. You'll be redirected to the Metrics Library page and the new metric will be displayed with the calculated value.
In our example, you'll see the "Total revenue" displaying the addition of values in the AMER, EMEA, and APAC metrics. Or, "Consolidated revenue" displaying the average of values in the AMER, EMEA, and APAC metrics.
If the values in AMER, EMEA, and APAC were from January till July, and the "sum of values" was chosen in the "Show total as" option while importing them, then the "Total revenue" will display the addition of all the values from January till July, and the "Consolidated revenue" will display the average value of all the months.
If "last value" was chosen while importing, then the "Total revenue" will display the addition of values in the month of July alone while the "Consolidated revenue" will display the average value for July for all the regions.
The Metric Library will now display 'calculated' on the metric tile to indicate the metric tree.
Metric trees only appear in the Metric Library unless they’re connected to a measure in a plan. To add this hierarchy within a plan, use submeasures. Learn more in Connecting Metric Trees to Measures for Submeasures
How to edit or delete the aggregate metric?
If you wish to add more metrics for aggregation to the existing aggregate metric, or remove a metric from the aggregation, then click the three dots on the aggregate metric tile and click Edit.
In the Display tab, you can edit or rename the metric, and update the trend to visualize how the metric is progressing.
You can edit or delete a metric either from the Metric tree tab of its sidebar or from the Metric trees page.
Click the metric and from its Metric tree tab, click Edit metric tree.
-
Select the check-boxes of the metrics that you need to remove from the relationship and click Remove selected.
-
Or, click the Trash icon against the relevant metrics and remove them.
-
Click the Operator and choose to change the option to Sum or Average from the dropdown.
-
Click Add metric and choose the metrics that you wish to add.
-
Click View metric tree to revert or click Save to see your changes reflected.
Or, click View full metric tree. This'll open up the metric in the Metric trees page.
-
Click on the metric tile that you wish to remove, and click the Trash icon.
-
Click the Operator and choose to change the option to Sum or Average from the dropdown.
-
To add a new metric to the existing tree, drag and drop the metrics from the left pane and assign the relevant relation to it.
-
Click Preview and then Save to see your changes reflected.
FAQs
If I make an edit to a data in the past in the imported metric, will it sync the value in the aggregate metric?
Yes, any changes to the historical data will automatically be synced with the relevant metric as well the aggregate metric. Click Sync now in the individual as well as the aggregate metric, if the changes are not reflected immediately.
Can I include manually created metrics for aggregation?
Yes, metrics either connected from a source or created manually in the Metrics library can be included for aggregation since all these will be housed in the metrics library.
Can I see an example with a data set?
Explaining the aggregate metrics with simplified values for your quick reference and understanding:
|
Metric |
AMER |
EMEA |
APAC |
|---|---|---|---|
|
January |
300 |
150 |
120 |
|
February |
280 |
220 |
150 |
|
March |
310 |
190 |
200 |
|
April |
320 |
200 |
180 |
|
May |
250 |
200 |
150 |
|
June (Last value) |
300 |
180 |
220 |
|
Total |
1760 |
1140 |
1020 |
When these AMER, EMEA and APAC are imported, then the metric tiles would display:
-
the values in the "June" row for "last displayed value"
-
and, the values in the "Total" for "sum of values"
Now, the aggregate metrics will display:
-
when "last displayed value" was chosen while importing AMER, EMEA, APAC metrics, then
-
Total revenue will be 700 (Sum of 300, 180 and 220)
-
Consolidated revenue will be 233 (Average of 300, 180 and 220)
-
-
when "sum of values" was chosen while importing AMER, EMEA, APAC metrics, then
-
Total revenue will be 3920 (Sum of 1760, 1140 and 1020)
-
Consolidated revenue will be 1307 (Average of 1760, 1140 and 1020)
-
The data in my spreadsheet is set up "monthly" and is imported in the same timeframe. But, I want the aggregate metrics to show quarterly data.
This is not possible if you've imported the metrics in the monthly timeframe. But, you can edit the imported metrics from their sidebar, and change the timeframe as quarterly. Once saved, your aggregate metric will show data points grouped in quarters.