Changing the aggregation and subtotal behavior for a derived metric

The aggregation and subtotal behavior for derived metrics is automatically determined by default. If you need a different calculation than the values provided by the default automatic behavior, you can use manual mode. Manual mode allows you to specify whether the derived metric is calculated using the whole dataset (the base) or the data in the visualization that it is placed on.

For example, a dataset contains Region, Category, and Subcategory, with a Revenue metric. The grid is subtotaled at the Category and Region levels, returning the maximum revenue in the category or region, as shown below. The subtotal values will help you understand the dynamic aggregation that occurs later. Dynamic aggregation is the roll-up of metric values that occurs when an attribute is removed from a visualization.

You create two derived metrics, returning the maximum revenue at the level of Region:

• The Max Revenue (visualization) metric uses the attributes on the visualization when dynamic aggregation is triggered.
• The Max Revenue (base) metric uses the attributes on the dataset (the base) when dynamic aggregation is triggered.

Remove the subtotals from the grid and add the derived metrics. Remove Subcategory from the grid, to trigger dynamic aggregation. The new grid is shown below:

Notice that the two Max Revenue metrics return different values. The Max Revenue (visualization) metric returns the maximum revenue value from the visualization. That is, the metric is choosing between the two revenue values for each region. The Max Revenue (base) metric returns the maximum revenue value at the subcategory level (which is the level of the dataset or base), the same as the subtotals calculated in the previous grid.

To Change the Aggregation and Subtotal Behavior of a Derived Metric

1. Open the dossier you want to modify.
2. Right-click a derived metric and choose Edit. The Metric Editor opens.
3. Click the Metric Options. The Metric Options dialog opens.
4. Select Manual from the Aggregation and Subtotal Behavior drop-down.
5. The derived metric aggregates at the level of the attributes on the visualization by default. To aggregate it at the level of its dataset, select the Aggregation From Base checkbox.
6. The derived metric subtotals at the level of the attributes on the visualization by default. To subtotal it at the level of its dataset, select the Subtotals From Base checkbox.
7. Click OK to return to the Metric Editor.
8. Click Save.