MicroStrategy ONE

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

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.

  1. Open the dashboard 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.

Related Topics

Introduction to Metrics

Introduction to Derived Metrics

Create a Derived Metric Using the Metric Function Editor

Create a Derived Metric On a Grid Using a Shortcut

Create a Derived Metric by Combining Two Metrics with an Operator

Create a Derived Metric by Combining Multiple Metrics

Create a Derived Metric by Selecting the Aggregation Function

Create a Derived Metric from an Attribute

Create a Derived Metric from Scratch Using the Metric Formula Editor

Add Conditional Calculations to a Derived Metric Using the If and Case Functions

Provide Statistical Analysis from R Analytics

Edit Derived Metrics from the Dashboard

Rename Derived Metrics from the Dashboard

Delete Derived Metrics from the Dashboard