Creating compound metrics

Metrics can be either simple or compound. The formula of a compound metric is based on arithmetic operators and non-group functions. The metric formula defines the data to be used and the calculations to be performed on the data. The operators and functions of a compound metric can be applied to facts, attributes, or metrics within the compound metric.

Arithmetic operators are +, -, *, and /.
Non-group functions are OLAP and scalar functions such as running sum or rank.

The following are examples of compound metric formulas:

Sum(Cost) + Sum(Profit)

where Cost and Profit are facts joined by an arithmetic operator
Cost + Profit

where Cost and Profit are metrics joined by an arithmetic operator
RunningAvg(Cost)

where Cost is a metric and RunningAvg is a non-group function

You cannot set the level, conditionality, or transformation for the entire compound metric. A compound metric draws this information from each of its component metrics; each component metric can have its own level (the level of calculation for the metric, such as year or customer), condition (a filter placed on the metric, separate from any report filters), and transformation (offset value, such as "four months ago").

However, compound metrics, unlike simple metrics, can have smart subtotals, which allow a different evaluation order for the calculation. For more information on smart subtotals (also called smart metrics), see Creating smart metrics. You can also control how the expression or metrics within the compound metric are joined. For more information, see Setting join types for metrics.

Compound metric example

The following report compares actual amounts to forecasted amounts. It is used to assess differences between projected amounts, referred to as forecasts, and the actual amounts for the selected time period. This report sample presents only a subset of the entire report, as it is a long report.

Report with compound metrics

The first two metrics, Actual Amount and Forecast Amount, are simple metrics. The Actual Amount is the sum of the fact Account Amount, which is the dollar value of all the accounts. The Forecast Amount is the sum of the fact Account Forecast Amount, which is a calculated value generally assigned to the accounts quarterly and usually adjusted monthly. These definitions are shown below:

Sum([Account Amount]) {~+}
Sum([Account Forecast Amount]) {~+}

The {~+} indicates that the metrics are calculated at the report level; for an explanation of how the report level affects metric calculation, see About the report level of a metric. See the table of symbols in Metric Editor Formula tab: Metric level property symbols.

The last two metrics on the report are compound metrics based on these simple metrics. The metric definition for Actual - Forecast Amount Difference simply subtracts Forecast Amount from Actual Amount, as shown below:

([Actual Amount] - [Forecast Amount])

The Actual - Forecast Amount Variance metric determines the ratio of the difference to the forecast amount, as shown below:

((1.00001 * ([Actual Amount] - [Forecast Amount])) / (1.00001 * [Forecast Amount]))

Notice the lack of symbols for levels, conditions, or transformations in the formulas of the compound metrics. If this information were present, it would come from the simple metrics. In this case, the compound metrics are calculated at the report level.

For other examples of compound metrics, see Creating compound metrics: Examples below.

To create a compound metric

Open the Metric Editor. (How?)
If the Object Browser (the list of folders and objects) is not displayed, select Object Browser from the View menu.
Create the formula by doing one of the following. To create a compound metric, you must use arithmetic operators or non-group functions in the formula. As shown in the examples at the end of this procedure.
- Type the formula in the Definition pane. You can use the Object Browser to locate facts, attributes, and metrics, then double-click them to add them to the formula. You can click an operator in the operator bar to add it to the formula.
  
  If you select a fact or attribute using the Object Browser, a default function is added along with the object. You can replace the function with the non-group function or operator of your choice.
- Click f(x) to create a function using the Insert Function Wizard.
To enable smart totals, which calculate subtotals on the individual elements of the metric, see Creating smart metrics.
When you have completed the metric definition, select it in the Metric component pane (located under the heading "Metric (metric name) is defined as"). Click Validate. The Metric Editor displays a status message at the bottom of the Definition pane, showing whether the new metric definition is correct (Valid expression).
Click Save and Close on the Metric Editor toolbar. The Save As dialog box opens.
Select the folder in which to save the new metric.
Enter the name of the new metric and click Save. You are returned to MicroStrategy Developer.

When naming a MicroStrategy object, you must follow the naming convention rules for your particular database platform. Using a word reserved by your database platform may result in an error. Refer to your database documentation for a list of these database-reserved words.