Nested Metrics

Nested metrics, or nested aggregation metrics, are a type of simple metric, where one aggregation function is enclosed inside another. For example:

Avg(Sum(Revenue))

The inner function Sum calculates the total for the Revenue fact, and the outer function Avg calculates the average of that result.

Nested metrics are useful when the data warehouse does not store fact data at the level needed for a specific analysis, and are best used with each formula aggregated at a different attribute level. For information on levels, see Level Metrics: Modifying the Context of Data Calculations.

For example, you want to know the average revenue per category for every region. You can create a nested metric that first calculates the revenue for all categories, and then calculates the average for each region. The metric is defined as:

Avg(Sum(Revenue) {~, Category}) {~, Region}

The inner formula, Sum(Revenue), calculates the revenue at the Category level. The outer formula then calculates the average of the resulting values at the Region level. In the Metric Editor, the inner formula appears as a child of the outer formula, as shown below:

The following report uses the above metric to display the average category revenue for each region.

This is similar to creating a report that calculates the revenue by category for each region, and then enabling the Average subtotal, as shown below. The value of the Average subtotal is reflected in each row of the previous report:

For steps, see Creating a Nested Metric.