Create a Derived Metric from Scratch Using the Metric Formula Editor

This topic is focused on creating a derived metric within a dashboard or report. (Derived metrics created within a dashboard/report are saved within the dashboard/report and therefore cannot be used outside of it.)

If you arrived here while creating a new stand-alone metric, see Metric Formula Editor. A stand-alone metric is saved as a separate object that can be placed on multiple reports and dashboards; those reports can be used as datasets on multiple dashboards and documents.
If you arrived here while creating a derived metric within a document, see Create a Derived Metric from Scratch in a Document.

You can create new metrics, called derived metrics, based on attributes and metrics that have already been added to a dashboard.

Creating a Derived Metric using the Metric Formula Editor

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 Strategy Function Plug-In Wizard. See the Functions Reference for more information.
Combine multiple metrics to create a single compound metric (for example, Revenue - Profit).
Create a metric that contains multiple layers of functions (a nested metric, such as Avg(Sum(Revenue)), which first adds all the Revenue fact values and then averages them.
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. When you 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. When you 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 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.

To Create a Derived Metric Using the Formula Editor

Do one of the following:
- Open the dashboard to modify. In the Datasets panel, right-click a metric and choose Create Metric. To add the derived metric immediately to a specific visualization, in the Editor panel for that visualization, right-click a metric and choose Create Metric.
- Open the report to modify. Right-click a metric in the Editor panel and select Create Metric.
The Metric Function Editor opens, with the selected metric and the default function of Sum added to the metric definition. Click Switch to Formula Editor.
In the right pane, type a name for the metric in the Metric Name field.

Click functions in the left pane to view a corresponding description at the bottom of the dialog box. 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.
To manually type the formula, in the Formula section, type 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. If you need to delete the metric formula and begin again, click Clear. When the formula is complete, Validate the Expression.
You can instead use the Functions and Objects panes to assist in the creation of your derived metric definition. Locate the functions and objects 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, and so on) from the drop-down list. Once you locate the functions and objects 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 display 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 dashboard visualization or report grid 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 the report or on any visualization that the metric is placed on. To change the level, select an attribute from the Level drop-down list. 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 box. See Introduction to Advanced Filtering for more information.
If the Sort By field displays, 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.
Validate the Expression
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

Creating a Derived Metric using the Metric Formula Editor

To Create a Derived Metric Using the Formula Editor

Related Topics