Create Aggregated Metrics with Metrics Calculations
With "metrics calculations", you can combine any metrics data connected to Cascade from any data source to create a new calculated or aggregated metric! This new aggregate metric will be the roll up of the related child metrics, and when connected to a measure, can have a sub-measure relationship with those measures connected to the relevant child metrics. With aggregate metrics, you can see how aligned are your connected metrics and have an improved business observability.
At this point, the calculations available are only additions and average. Some examples of when you can have aggregate or calculated metrics are: Total revenue, Consolidated EBITDA margin and net profit margin, Average sales per month or product, Average defect rate of products, Total sales revenue from different products and regions, Average TPL costs, Total or Average downtime, and so on.
From the Metrics > Library tab, click + Create new metric and select Aggregate metrics.
Before you begin, for metrics calculation,
- the relevant metrics which are needed for calculating the new metric, must already be imported or created manually in the Metrics library.
- you need to have atleast 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!
How to create an aggregate or calculated metric?
Once you choose the Aggregate metrics option, you'll see the list of imported metrics in the left pane. 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!
- Drag and drop the New metric tile from the left pane to the right pane, and give the new aggregate metric a relevant title or name! And, select the 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, or "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.
- Click on the red dot in the metric tiles and drag the connector to connect the chosen metric to the new aggregate metric. Then, select the type of calculation - addition or average by clicking on the relevant options! Hover the mouse over the metric, and click 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 Avg.
- Click Undo if you wish to revert the latest action or if everything is right, 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.
- 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.
You can add any number of layers of calculation and every new aggregate metric will be displayed in individual tiles. Click the aggregate metric tile to view its details from the sidebar:
- Details - Displays a line graph of the aggregate metrics' progress against time.
- Measures - Here, you can either create a new measure from this metric, or you can view the measures that're connected with this metrics. See Connect your Imported Metrics to Measures for details.
- Table view - Displays the metrics chosen for aggregation and the calculated values over time.
Now, you'll have the ability to then connect this new metric, and the ones being aggregated, to subsequent measures to enable progress roll-up. Click on the new aggregate metric tile and from the three dots, click Connect metric to measure.
A quick example:
Explaining the aggregate metrics with simplified values for your quick reference and understanding:
|June (Last value)||300||180||220|
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)
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!
In the Source data tab, click
- + Add metric and select a metric from the list, if you wish to add a metric to the aggregation.
- (Unlink metric) icon to remove a metric from the aggregation.
- Addition of or Average of if you wish to change the type of aggregation.
Once done, click Save to see your changes reflected.
To delete an aggregate metric:
- Click the three dots on the aggregate metric tile, and click Edit.
- In the Source data tab, click against all the metrics.
- Click Save.
- Click the three dot on the aggregate metric tile, and click Delete.
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 or added while creating a measure with manually tracked (new metric) tracking option can all be included for aggregation since all these will be housed in the metrics library!
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!