MicroStrategy ONE
Level metrics: Modifying the context of data calculations
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 level of data aggregation.
About level metrics
By default, metrics are evaluated at the level of the attributes on the report; this is called the report level. For example, a revenue metric is set to the report level. 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 revenue is calculated for each customer.
You can set the level within the metric, allowing you to specify the attribute(s) to use in the metric calculation, regardless of what is contained on any report the metric is placed upon.
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 products bought by your top customers. Level metrics allow you to create contribution metrics, as shown in the following report, which can be helpful for marketing and customer service.
The Revenue and Profit metrics are familiar; they are simply the sum of the Revenue fact or the Profit fact. On the surface, the contribution percentage metrics should be just as easy—product revenue divided by all revenue, or product profit divided by all profit. But how can you calculate at two different levels in the same metric? The answer is a level metric, which allows you to specify how the metric is calculated.
For example, the % of All Revenue (by Product) metric used in the example above divides the Revenue metric by a metric called Revenue (Dimensionality All Products). The Revenue metric is calculated at the report level, in this case, for each product. A different number is calculated for each row of the report. In contrast, the level of the Revenue (Dimensionality All Products) metric has been set to product, which means that it reflects the revenue of all products. The same number is calculated for each row of the report, as shown below.
The level is indicated between the curly braces ({ }) in the metric definition shown below:
Sum(Revenue) {~, Product}
The tilde (~) indicates that the report level is still part of the metric level. For information about how the report level provides flexibility and the effects of removing it, see About the report level of a metric.
Report level: the default level
By default, a metric is calculated at the report level, meaning that the lowest attributes on the report dictate how the metric is aggregated. For example, if a report contains revenue by year and month, the numbers are calculated to reflect monthly sales data. For a more detailed description, see About the report level of a metric.
The concepts underlying the term level in the context of MicroStrategy metrics are interchangeable with those of dimensionality. The term level is used throughout the documentation.
Elements of a metric level
The elements needed to specify a level for a metric are described below:
-
Target: The target is the attribute level at which the metric calculation groups. For a more detailed description, see The context of a calculation: Level metric target.
-
Grouping: Grouping determines how the metric aggregates. For a more detailed description, see Defining how aggregation is performed: Level metric grouping.
-
Filtering: Filtering governs how the report filter interacts with the metric calculation. For a more detailed description, see Interaction with report filters: Level metric filtering.
A target, grouping, and filtering combination composes one level unit.
These elements are set in the Metric Editor, using the metric definition window, which displays the complete metric definition, including its formula, level, condition, and transformation. When you select Level (Dimensionality) in the upper portion of this window, the Level (Dimensionality) component window appears below the metric definition area.
Clicking Reset changes the level unit back to the default of report level for the target and standard for filtering and grouping. The Add Report Level button becomes available if the report level has been removed from the metric level.
The Advanced button accesses the Level (Dimensionality) advanced options dialog box, which is discussed in Excluding filter attributes: Level metric advanced options.
Since level metrics are a complex subject, they are only briefly described in this topic. For a more detailed discussion, including examples, see Level metrics: A practical overview and the Advanced Metrics chapter of the Advanced Reporting Help.