Create a Derived Metric from Scratch Using the Metric Formula Editor

If you arrived here from Workstation, see the Workstation Document Authoring Help.

Creating a derived metric from scratch allows you to customize the metric and make it more sophisticated to fit your needs. You can perform the following tasks:

Build custom expressions, such as percent-to-total metrics or expressions that use custom functions created with the MicroStrategy Function Plug-In Wizard. See the Functions Reference for more information.
Create a metric by typing the metric formula directly. You can add conditional calculations, to create groups of data by filtering metric values. In addition, you can group regions into poor revenue producers and good revenue producers. This supplements the conditional analysis provided by filters and thresholds.
Perform statistical analysis by creating derived metrics based on R analytics.

To create a metric, you must define the metric's formula, which consists of arithmetic operators (+, -, *, and /) and functions. The operators and functions can be applied to attributes or metrics. An example of the formula of a compound metric is RunningAvg(Cost), where Cost is a metric and RunningAvg is a function that calculates the running average. Another example is Sum(Cost) + Sum(Profit), where Cost and Profit are metrics and Sum is a function that calculates the sum. In this case, the summation of the Cost metric is added to the summation of the Profit metric.

You can create a nested metric, where one aggregation function is enclosed inside another. A sample metric formula is Avg(Sum(Revenue)). The inner function Sum calculates the total for the Revenue fact, and the outer function Avg calculates the average of that result. If you want to know the average revenue per category for every region, you can create a nested metric that first calculates the revenue for all categories, and then calculates the average for each region. The metric is defined as: Avg(Sum(Revenue) {~, Category}) {~, Region}

The inner formula, Sum(Revenue), calculates the revenue at the Category level. The outer formula then calculates the average of the resulting values at the Region level.

To add functions to the metric formula, you must select any options required to define the function. If you want to add a grouping function, such as Sum, Average, First, or Max, you must define the function's expression, and you can also change the attribute level that the metric aggregates at. If you want to add a non-grouping function, such as data mining, date, OLAP, and ranking functions, you must define the input values (called arguments) for the function, as well as any parameters you can use to determine the behavior of the function.

See in the Advanced Reporting Help for the syntax of metric formulas that use grouping functions. See the Functions Reference for the syntax of non-grouping functions.

Open the dossier you want to modify.
Create a derived metric and reserve it for future use in a visualization. In the Datasets panel, click More to the right of the dataset in which you are creating a derived metric and choose Create Metric.
Click Switch to Formula Editor.
In the right pane, enter a name for the metric in the Metric Name field.

Click on functions in the left pane to view a corresponding description at the bottom of the dialog. Then, click Details to view more information about the function and its arguments in the Functions Reference . See Add Conditional Calculations to a Derived Metric Using the If and Case Functions for more information about the If and Case functions.
In the Formula section, enter the derived metric definition. You can type arithmetic operators (such as + and -) and values directly in the metric expression or select them using the corresponding buttons. Go to step 12.

or

Use the Functions and Objects panes to assist in the creation your derived metric definition. Locate the functions and objects you want to add by using the Search field at the top of each pane. In the Functions pane, you can also browse for a function by selecting a function category (that is, Basic Functions, String Functions, etc.) from the drop-down. Once you locate the functions and objects you want to use, double-click them to add them to the derived metric definition in the right pane.
If you selected an aggregation function (that is, grouping function), such as First, Maximum, or Sum, the Level and Filter fields appear under Function Parameters.
You can define function parameters, such as whether to use all values in the calculation or calculate using only unique values. Click Function Parameters, select the appropriate options to define the parameters, and click OK.
The function is calculated at the level of the attributes on the visualization on which the metric is placed, by default. You can specify the attributes to use in the metric calculation, regardless of what is contained on any visualization that the metric is placed on. To change the level, select an attribute from the Level drop-down. You can also add multiple attributes.

If a visualization contains Region and Category, by default the metric calculates regional and category values on that visualization. If you select Region as the level, the metric calculates the regional revenue values on that visualization; it does not include a breakdown by Category. This allows you to compare revenue across regions.
You can add advanced filtering to your metric. Click Add Filter to open the New Qualification dialog. See Introduction to Advanced Filtering for more information.
If the Sort By field appears, select options for sorting the data.
Click Add to add the function to the metric. The function, including the arguments you provided, appears in the right pane of the Metric Formula Editor.
If you need to delete the metric formula and begin again, click Clear.
Click Validate to check if the metric expression is valid.
Click Save.

Aggregation and subtotal behavior is automatically determined by default. You can change that behavior, to specify whether the derived metric is calculated using the whole dataset or calculated using the data in the visualization that it is placed on. See Change the Aggregation and Subtotal Behavior for a Derived Metric for more information.

Create a Derived Metric from Scratch Using the Metric Formula Editor

Related Topics