Create Derived Metrics in Reports

You can create a new metric while you are viewing a report. For example, you can subtract the values of one metric from the values of another metric, such as Revenue Forecast - Revenue. You can calculate a monthly average, for example, Yearly Profit/12. If the report shows the dollar sales for a particular region, you can create a derived metric to view the same data in millions, defined as Dollar Sales/1,000,000.

Derived metrics are metrics that a report analyst can create based on existing metrics on the report. A derived metric performs a calculation on the fly with the data available on a report, without re-executing the report against the data source. Derived metrics are not metric objects that can be re-used in other reports, dashboards, and documents. They are metrics that are saved and displayed only on the specific report on which they are created.

You can only use metrics already contained in the report to create a derived metric. If the derived metric requires data not available on the report or in the Objects panel, the report must be re-executed against your data source before the new data displays.

You can create derived metrics in reports, as described below.

The steps below show you how to create a derived metric by selecting objects and being guided through the process, using the Metric Function Editor. If you want to type the metric formula directly, or to create a metric that combines multiple metrics or contains custom expressions, see Creating a Derived Metric Using the Metric Formula Editor.

Prerequisites

• You must have the necessary privileges to add or remove metrics and calculations.

• The report in a document must display as a grid or as both a grid and a graph.

To Create a Derived Metric on a Report

1. On any page in Library, search for or navigate to the report. Right-click the report and select Edit.
2. 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. You can instead use the Metric Formula Editor to type the metric formula directly or to create a metric that combines multiple metrics or contains custom expressions.
3. Define the metric.

To Define the Derived Metric

1. In the Function Editor, locate the metric's function by typing its name in Search. 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.

   • To view a description of a function, click it in the left pane; its description displays at the bottom of the interface. Click Details to view more information about the function and its arguments in the Functions Reference . See Add Conditional Calculations to a Derived Metric for more information about the If and Case functions.

2. Click a function to add it to the derived metric definition in the right pane.
   • If you select an aggregation function (that is, a grouping function), such as First, Maximum, or Sum, the Level and Filter fields display under Function Parameters.
     1. The function applies to the metric you originally selected. From the drop-down list, you can select a different metric or an attribute element.
     2. 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.

     3. The function is calculated at the level of the attributes on the report grid, by default. You can specify the attributes to use in the metric calculation, regardless of the attributes on the report grid. To change the level, select an attribute from the Level drop-down list. You can also add multiple attributes.

        For example, if the grid contains Region and Category, by default the metric calculates regional and category values. If you select Region as the level, the metric calculates the regional revenue values; it does not include a breakdown by Category. This allows you to compare revenue across regions.

     4. If the Sort By field appears, select options for sorting the data.
   • If you select a non-aggregation function, such as data mining, date, OLAP, and ranking functions, options display that allow you to define the input values (that is, arguments) for the function, as well as any parameters that determine the behavior of the function. By default, the metric you originally selected is used in the derived metric definition, but can select a different metric or an attribute form from the drop-down list. Define the other arguments and parameters.
3. Type a name for the metric in the Metric Name field.
4. Click Save.

The new derived metric displays on the Editor panel with the derived metric icon . It is added to the report grid as well.

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 grid that it is placed on. See Change the Aggregation and Subtotal Behavior for a Derived Metric for more information.