Strategy One
Level Metrics: Changing the Level of Calculation for Metrics
Before You Begin
You must understand aggregating or calculating data at various levels of the business concepts related to a report's results. For a refresher on data aggregation levels in MicroStrategy, see the Advanced Reporting Help.
About Level Metrics
Level metrics are metrics that are evaluated at a set level of data, regardless of what is contained on the report they are placed on. By default, metrics are evaluated at the level of the attributes on the report on which they are placed (called the report level). For example, a revenue metric is evaluated at the report level by default. When the metric is placed on a report containing Region, the metric calculates regional revenue. When it is placed on a report containing Customer, the metric calculates the revenue for each customer. In contrast, if you create a level metric with revenue evaluated at the Year level, the metric calculates yearly revenue when placed on either report.
Level metrics are useful for determining the contribution of one object to the whole. For example, you need to determine the contribution to revenue and profit of each individual product that you sell.
In the example above, the Revenue metric is calculated at the Product level, and each revenue value displayed in the report contains the revenue generated by the sale of a specific product, such as Ladies Home Journal or Seventeen magazine. To calculate the contribution of each product to the total revenue, however, you need to divide the revenue at the product level by the revenue taken across all products at once. You do this using level metrics, which allow you to specify the level at which metrics are calculated.
In the example below, the Revenue metric is calculated at the report level, for each product. In contrast, the Revenue (Dimensionality All Products) metric displays the revenue of all the products combined. The contribution metric, % of All Revenue (by Product), is created by dividing the Revenue metric by the Revenue (Dimensionality All Products) metric.
The level of a metric, also called its dimensionality, is made up of the following components:
- Target:
The attribute level at which the metric calculates. The target can
be an attribute or a hierarchy. Using a hierarchy allows the level
metric to dynamically adapt to the report. When placed on a report
with attributes from that hierarchy, the metric calculates at the
level of the lowest attribute of that hierarchy. For specifics on
using a hierarchy as a target, see the Advanced Reporting
Help.
If you specify an attribute as the calculation level of a metric, the metric-specified attribute level overrides the default report level. For information about how the report level provides flexibility and the effects of removing it, see the Advanced Reporting Help. - Grouping: Determines how the metric aggregates. For example, you can choose to group and calculate metric data based on the attribute selected as the target.
- Filtering: Determines how the report filter affects the calculation of the metric. For example, if a report displays Revenue metric data across different product categories and the report filter only includes Literature and Travel, you can choose to include only data from Literature and Travel when the metric is calculated.
You can select options to define a level metric, including how the report filter affects the data included in the metric and how metric values are grouped, using the options in the Level Options dialog box. A level metric allows you to specify the level of attribute (referred to as the target) to use when the metric is calculated, regardless of what is contained on any report the metric is placed upon.
The following options are available:
- Relationship with report filter:
Determines how the report filter affects the calculation of the metric.
The options are:
- Standard (default): Only data that meets the conditions in the report filter is included in the metric calculation.
- Absolute: Raises the level of the report filter to the level of the target, if possible, then applies the report filter to the metric calculation. For example, the report filter contains the Washington, DC, Boston, and New York call centers, but the Revenue metric is calculated at the Region level. Because Call Center is a child attribute of Region, the report filter's level is raised to the Region level, and the report filter is treated as if it includes the regions that contain Washington, DC, Boston, and New York (in this case, Mid-Atlantic and Northeast). Data from all call centers in the Mid-Atlantic or Northeast regions are included in the metric calculation, including call centers that are not Washington, DC, Boston, or New York.
- Ignore: Ignores any conditions in the report filter that are based on the target attribute, as well as any parent or child attributes of the target. For example, if you have a regional revenue metric on a report, any conditions based on Country, Region, or Call Center in the report filter are ignored when calculating the metric. However, a condition based on Year would not be ignored, since Year is not directly related to Region.
- None:
Leaves the metric's relationship with the report filter unspecified.
Instead, the target and grouping options define
the metric level.
- If the report includes an attribute in the same hierarchy as the metric's target, the metric is aggregated at the level of the report attribute.
- If the report does not include other attributes in the same hierarchy as the metric's target, the metric is aggregated as if the Absolute option was selected.
- Metric aggregations: Determines
how the metric is grouped, or aggregated, when displayed on a report.
The following options are available:
- Standard (default): Groups data in the metric by the attribute level of the target.
None: Excludes the target attribute from being used to group data in the metric. Any children of the target attribute are also excluded. This option is available for metrics calculated at a set level, as opposed to the report level.
The following options are only used for nonaggregatable metrics. A nonaggregatable metric, such as an inventory metric, is one that should not be aggregated across an attribute. For example, if you have monthly inventory numbers in your data warehouse and want to calculate the yearly inventory, adding the monthly numbers together does not provide a useful business measure. Instead, you may want to use the end-on-hand and beginning-on-hand inventory numbers to see how the total inventory changed during the year.
- Beginning lookup: Uses the first value in the lookup table.
- Ending lookup: Uses the last value in the lookup table.
- Beginning fact: Uses the first value in the fact table.
- Ending fact: Uses the last value contained in the fact table.
You can define advanced options related to metric filter and metric calculation interaction.
- Allow other users to add extra units to this definition: Determine whether the metric accepts dimensionality units. This option is applied to metrics used at the template level and metrics used in the filter for a metric qualification. The dimensionality is merged with the original units to complete the metric level. This option is selected by default.
- Include filter attributes which are not in report or level in metric calculation: Determine whether to apply the metric filter when the metric is calculated. If this option is cleared (as it is by default), filter attributes that are not on the report or in the level of the metric are not included in the metric calculation. This option lets you determine which parts of the metric filter are applied based on the data that has been included on the report. It can help you reuse the same metric in multiple reports, eliminating the need to create and maintain multiple metrics, particularly if the metric and filter qualifications are complex.
Levels in nested metrics
Beginning in Strategy One (June 2025), the Metric Editor in Workstation allows you to add levels to all layers of nested metrics.
Report Level: Interaction with the Context of Reports
When you create a metric, the level is set by default as the report level. The metric calculates at the level of the attributes on the report on which the metric is placed.
Keeping the report level allows the metric calculation to adapt to the report. Consider a metric that sums the Revenue fact at the report level and at the Item level.
-
When you place this revenue metric on a report containing region, that attribute affects the metric calculation, along with the level explicitly set on the metric (item).
-
When you put the same metric on a report with customer, that attribute, as well as the metric level, is used in the metric calculation.
In other words, the report level in the metric level tells the engine to group by all the attribute IDs on the report. The content of the report is reflected in the metric calculation.
Removing the Report Level
If the report level is removed, only the level explicitly set on the metric affects the metric calculation, regardless of what the report contains. For example, remove the report level from the revenue metric described previously. Place this metric on a report with the Region attribute and two other revenue metrics, one with report level only and the other with report level and item. The report results are shown below.
The Revenue: Report Level and Revenue: Report & Item metrics calculate the same result, providing the revenue for each region. The Revenue: Item metric calculates the revenue for all items, for all regions, in each row. The number is therefore the same for each row, since the metric does not differentiate between regions. The number is the same as the grand total of the report.
Related Topics
-
Create a Stand-Alone Metricfor steps to create metrics, including level metrics