MicroStrategy ONE
How to Create a Metric
You can create and add metrics to a MicroStrategy project to perform calculations on your business data.
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. Your metric may contain multiple functions.
- Expression: The business data from your data source. The expression can contain business facts, attributes, or other metrics.
If you are creating multiple metrics and each metric uses the same basic calculation as part of its formula, you can save time by creating a base metric to perform the calculation. You can then define additional metrics based on the base metric instead of typing the same calculation multiple times.
For example, you want to create a Profit metric and a Profit Margin
metric. You can create a Revenue metric which calculates Price
x Quantity
. You can then define the Profit metric as Revenue
- Cost
, and the Profit Margin metric as Profit/Revenue
.
If you decide to change how Revenue is calculated later on, you can
make changes to the Revenue metric; all metrics defined using the Revenue
metric, including Profit and Profit Margin, will be updated to reflect
the change. You create base metrics using the same steps as you would
use to create any other metric. When creating metrics using the base metric,
you type the base metric as part of the metric formula.
The steps below show you how to create a basic metric. If you want to type the metric formula directly, or to create a metric that combines multiple metrics or contains custom expressions, see Metric Formula Editor.
Familiarity with the following, as applicable to the metric that you want to create:
- See the Basic Reporting Help for an introduction to metrics and some basic examples
- See the Advanced Reporting Help for more detailed steps to create a metric, including examples of level, conditional, and transformation metrics
- See Functions Reference for descriptions of functions, including syntax
To Create a Metric
- Click Create on any page, and select New Metric. The Select a Function or Template dialog box opens.
- In the pane below, select the
function to use to calculate data in the metric. You can narrow the
list of functions displayed in the pane by doing one of the following:
- To search for the function by name, type the function's name in the search field.
Choose a function category from the drop-down list, such as Math Functions or Financial Functions. The pane is updated to include only the functions that belong to the selected category.
When you select a function, a description of the function is displayed at the bottom of the dialog box. Click Details to view more information about the function, such as syntax and examples.
-
The Function Editor opens, with different options available depending on the type of function you selected above:
If you selected a grouping function, such as Sum, Average, First, or Maximum, you define the metric's expression, as well as optional components such as the level, condition, and transformation. Perform the following steps:
- Define
the metric's expression by doing one of the following:
- To
specify the expression by typing the name of an object,
type the name of the object in the Expression
field. As you type, matching objects are displayed in
a drop-down list. You can type multiple objects, such
as
Revenue-Profit
. - To specify the expression by choosing an object, click the Browse icon . The Select an Object dialog box opens. Navigate to and select an object, or search for the object.
- To
specify the expression by typing the name of an object,
type the name of the object in the Expression
field. As you type, matching objects are displayed in
a drop-down list. You can type multiple objects, such
as
- You
can further define the metric by adding a level, condition,
and transformation. Choose from the following:
- To define the attribute level at which the metric is evaluated, you add a level to the metric. 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 . . A level allows you to specify the attributes to use in the metric calculation, regardless of what is contained on any report the metric is placed on. By default, a metric is calculated at the level of the attributes on the report.
- To filter the data included in the metric, you add a condition to the metric. For steps, see Add the Condition. Metric conditions (also called metric filters) are applied to the metric without affecting any other metrics on a report. For information on report filters, which affect multiple metrics on a report, see Introduction to Filters.
- To apply offset values such as "four months ago" to a metric, you add a transformation. For example, you can compare revenue numbers from this year to last year. For steps, see Add a transformation.
If you selected a non-grouping function, such as data mining, date, OLAP, and ranking functions, you are presented with options to define the input values (called arguments) for the function, as well as any parameters you can use to determine the behavior of the function. For example, the NTile function has two parameters, Ascending and Tiles. Ascending controls whether the NTiles are ordered in ascending or descending order, while Tiles sets the number of splits. To view a list of the arguments and parameters for the function, click Details at the bottom of the dialog box.
Perform the following steps:
- For each argument listed, type a value or click the Browse icon to find the metric, fact, prompt, or other compatible object to use as input values of the function.
- For each parameter listed, type a value or select the parameter value from the drop-down list.
- Define
the metric's expression by doing one of the following:
- 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.
- You can select the functions that can be used to total the metric on a report. See Advanced Metric Options Dialog.
- You can select the function used for the rollup of metric values that occurs when an attribute is moved from the report grid to the Report Objects in a report. For steps, see Advanced Metric Options Dialog.
- Click Save.
Once you have created a metric, you can add the metric to a report to analyze your data. For steps, see How to Create a Report.
Related Topics
Introduction to Metrics for a brief overview of metrics
Format Dialog Box for Metrics for steps to format a metric
Advanced Metric Options Dialog for descriptions of advanced metric settings, such as dynamic aggregation and subtotal functions, VLDB properties, metric aliases, and the metric join type