MicroStrategy ONE
Conditional Metrics: How to Filter Data Calculations
A conditional metric allows you to apply a filter (called a metric filter or metric condition) to only one metric on a report without affecting the other metrics. The metric filter can be either a filter or a prompt that returns a list of filters. Only one filter or prompt can be associated with each metric, but the metric filter can contain multiple qualifications.
For example, you need to review regional revenue for different product categories. You can create a report containing Category, the Revenue metric, and conditional metrics for the regions. In the following report sample, the Northeast Revenue and Mid-Atlantic Revenue metrics are conditional metrics. Northeast Revenue uses the same formula as the Revenue metric, but contains a filter for the Northeast region. Similarly, the Mid-Atlantic Revenue metric contains a filter for the Mid-Atlantic region.
For more complex examples involving conditional metrics, such as a conditional metric that contains multiple qualifications or a level, see the Advanced Reporting Help. For a general overview of metrics, see Introduction to Metrics.
To create a metric, you must define the metric's formula, which consists of the following:
- Function: The calculation applied to your business data, such as Sum or Count. Depending on how you create the metric, your metric may contain multiple functions.
- Expression: The business data from your data source. The expression can contain business facts, attributes, or other metrics.
Steps are below to create a conditional metric. If you want to create the metric by defining the metric's formula directly, see Metric Formula Editor.
To Create a Conditional Metric
- Click Create on any page, and select New Metric.
- From the drop-down, select Metric Templates (at the bottom of the list). Select Condition.
- Click Next.
- From the Aggregation Function drop-down, select the function to use to create the metric.
-
Define the metric expression by doing one of the following:
- To type
objects to add to the expression directly, type the name of the
object in the Expression
field. As you type, matching objects are displayed in a drop-down. You can click an object or continue to type. You can type
multiple objects, such as
Revenue-Profit
. - To specify the expression by choosing an object, click the Browse icon . Either navigate to and select an object, or search for an object.
- To type
objects to add to the expression directly, type the name of the
object in the Expression
field. As you type, matching objects are displayed in a drop-down. You can click an object or continue to type. You can type
multiple objects, such as
Add the Condition
- If the options in the Condition area are not displayed, click Condition.
-
Select the filter or prompt to apply to the metric using one of the following methods:
- To type the filter or prompt directly, begin typing the name of the filter or prompt in the field in the Condition area. As you type, matching objects are displayed in a drop-down. You can click an object or continue to type.
- To define the filter or prompt by choosing an object, click the Browse icon in the Condition area. Either navigate to and select a filter or prompt, or search for the filter or prompt.
- You can determine how the metric condition interacts with the report filter, such as the order in which the report filter and the metric filter are applied to the metric data. Click the Condition Advanced Options icon in the Condition area, then select the appropriate options in the Condition Advanced Options dialog box.
- You can define how the metric header and the metric values are formatted and displayed in a report. For example, you can define how numeric values are displayed, font styles and sizes, and cell display colors. Your formatting will be applied to the metric regardless of the report on which it is placed. For steps to format the metric, see Format Dialog Box for Metrics.
- Click Save.
-
You can add levels or transformations to the metric, as follows:
- By default, a metric is calculated at the level of the attributes on the report on which the metric is placed. You can define the attribute level to use in the metric calculation, regardless of what is contained on any report the metric is placed on. For steps, see By default, metrics are evaluated at the level of the attributes on the report; this is called the report level. The report level allows the metric calculation to adapt to different reports. You can remove the report level from the metric. If you do this, only the level explicitly set on the metric affects the metric calculation, regardless of the attributes placed on the report. You do not have to remove the report level to add levels to the metric. For a more detailed description of the report level, and the impact of removing it, see the Advanced Reporting Help. Do one of the following:To remove the report level from the metric, click the Delete icon next to Report Level. To add the report level to the metric after it has been deleted, click the Add Report Level icon . . For a more detailed description of levels, including examples, see Level Metrics: How to Modify the Context of Data Calculations.
- A transformation is an attribute element-based offset that you can use to compare data. For example, a transformation metric can help a user compare last month's revenue to this month's revenue. For steps to add transformations, see Add a transformation. For a more detailed description of transformation metrics, including examples, see Transformation Metrics: Time-Based and Other Comparisons.
Related Topics
Condition Advanced Options Dialog Box for descriptions of the report/metric filter interaction and related report filter elements options
Introduction to Metrics for background information about metrics in general