Level Metrics: Modifying the context of data calculations

Prerequisite

You must understand aggregating or calculating data at various levels of the business concepts related to a report’s results.

About level metrics

By default, metrics are evaluated at the level of the attributes on the report; this is called the report level. For example, a Net Sales metric is set to the report level by default. When this metric is placed on a report containing Region, the metric calculates regional net sales. When it is placed on a report containing Customer, the net sales is calculated for each customer.

You can set the level within the metric, allowing you to specify the attribute to use in the metric calculation regardless of what is contained on any report on which the metric is placed.

Level metrics are useful for determining the contribution of one object to the whole.

How can you calculate at two different levels in the same metric? The answer is a level metric, which allows you to specify how the metric is calculated.

For example, the Refund Count-Cashier Pct of Store metric divides the Refund Count metric by a metric called Refund Count for Store. The Refund Count metric is calculated at the report level, in this case, for each cashier. A different number is calculated for each row of the report. In contrast, the level of the Refund Count for Store metric has been set to store, which means that it reflects the refund count for the store. The same number is calculated for each cashier in the same store.

The level is indicated between the curly braces ({ }) in the metric definition shown below:

Sum([Refund Count]) {~, Store}

The tilde (~) indicates that the report level is still part of the metric level.

Note: Since level metrics are a complex subject, they are only briefly described in this topic.

Report level: the default level

By default, a metric is calculated at the report level, meaning that the lowest attributes on the report dictate how the metric is aggregated. For example, if a report contains Refund Count by Store and Cashier, the numbers are calculated to reflect cashier level data.

Note: The concepts underlying the term level in the context of metrics are interchangeable with those of dimensionality. The term level is used throughout the documentation.

To add a level to a metric:

Prerequisite

The following procedure creates a level metric by using the Level metric template, which allows you to quickly and easily begin creating the metric. The Level template uses the Sum function and opens the Level area so that you can quickly define the level to apply to the metric. You only have to select which fact to sum as well as the level to create the metric.

You can instead create the metric by defining the metric's formula (the function and expression) yourself. The metric must use a grouping function (such as Sum or Average) to have a level added to it. For steps to create a metric that uses a grouping function, see Creating a metric that uses a grouping function. If you have already defined the metric formula, begin this procedure at Remove the report level (optional).

Navigate to the Shared Reports or My Reports folder or to the Search page.
From the icon bar, click the Create Metric icon. This displays the Select a Function or Template dialog box.
From the drop-down list, select Metric Templates.
From the list below, select Level, and click Next. This displays the Function Editor. The Aggregation Function defaults to Sum. You can keep this selection, or make a different one from the list.
The expression of a metric is what the function is applied to. The expression can be a fact, an attribute, or another metric. Specify the expression by doing one of the following:
- To specify the expression by typing the name of a 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 click an object or continue to type. You can type multiple objects, such as Net Sales-Refund Amount.
- To specify the expression by choosing an object, click the Browse icon . The Select an Object dialog box opens. Either navigate to and select an object, or search for an object.
Remove the report level (optional)

Note: If the Level area is not already open, expand the Level option.
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.
- To remove the report level, click the Delete icon next to Report Level.
- To replace the report level if you deleted it, click the Add Report Level icon
Add a new level
To determine the target, which is the attribute level at which the metric is calculated, click . The Select an Object dialog box opens. Select the target. You are returned to the Function Editor.

Note: The target can be an attribute or a hierarchy. Using a hierarchy allows the level metric to dynamically adapt to the report. When placed on a report with attributes from that hierarchy, the metric calculates at the level of the lowest attribute of that hierarchy.
Click the Level Options icon next to the target that you added. This displays the Level Options dialog box.
By default, the metric calculates only for the elements found in the filter definition (standard relationship with the report filter). To change how the report filter interacts with the metric calculation, select one of the following Relationship with Report Filter options:
- To allow the report filter to interact as usual in the metric calculation, select Standard filtering. The metric calculates only for the elements found in the filter definition.
- To change the filter on descendents of the target, by raising it to the level of the target, if possible, select Absolute filtering. Absolute filtering influences what is displayed on the report, not its calculations.
- To omit filtering criteria based on the attribute in the target and its related attributes (parents and children), select Ignore filtering.
- To allow the target and group components of the level to define the filter, select None. This option can be summarized as unspecified—the filtering behavior for the target is not determined by this component.
By default, the metric aggregates by grouping by the target attribute (standard metric aggregation). To change the aggregation element, select one of the following Metric Aggregations options:
- To group by the attribute level of the target, select Standard. The metric calculates at the level of the target, if possible.
- To exclude the attribute in the target from the GROUP BY clause in the SQL statement, select None. It also excludes any of the target attribute’s children. None is not an option if the metric level is the report level.
- The remaining options are only used for nonaggregatable metrics. A nonaggregatable metric is one that should not be aggregated across an attribute.
- - To use the first value of the lookup table, select Beginning lookup.
  - To use the last value of the lookup table, select Ending lookup.
  - To use the first value of the fact table, select Beginning fact.
  - To use the last value of the fact table, select Ending fact.
Click OK to save your changes. You are returned to the Function Editor.
You can add more levels to the metric, if needed. Repeat the steps above, beginning at To add a new level.

Complete the metric
By default, the metric filter is applied to the metric calculation. For a description of this option and steps to change it, see Level Advanced Options dialog box.
You can add a condition or transformations, or save the metric.
- To steps to add a condition, see To add a condition to a metric. A condition allows you to apply a filter to only one metric on a report while not affecting the other metrics. For a more detailed description of conditional metrics, including examples, see Conditional metrics.
- For steps to add transformations, see To add a transformation to a metric. A transformation is an attribute-element offset for fact-comparison purposes. For example, a transformation metric can help a user compare last month's net sales to this month's net sales. For a more detailed description of transformation metrics, including examples, see Transformation metrics.
- To save the metric, click Save to save your changes. The Save As dialog box opens. Navigate to the folder where you want to save the metric. Type a Name and Description for the metric, and click OK. Your new metric is saved, and you are returned to the Function Editor.