Metric to perform count based on result of another metric

Working on a project i needed to count how many times a metric was returning either 0 or more than 200%. Looking for ideas on how to build such metric i came accross this tech note in the MSTR KBase TN: 39189

The technical note game the bases to build the metric I needed.

This example shows how to build a metric that will perform a count based on the result of another metric.

I will use the tutorial project to create a simple example.

Let's say we have the following grid displaying Call Center and Discount %

We can create a metric that counts how many call centers had a "Discount %" greater than 3%

• Create a new embedded metric (Right click in Report Objects Window select Insert->New metric)
• Add the following formula to the metric: IF(([Discount %] > 0.03), 1, 0)

The formula of the metric has a simple IF statement that outputs a 1 if the condition, in this case "Discount %" metric value is higher than 0.03.

The output of the above metric can be seen here:

If we need a count of the total the metric can be modified to show the total instead of the individual values that meet the condition:

• Edit the metric formula with Sum(IF(([Discount %] > 0.03), 1, 0))

The if condition can also include AND and OR modifiers to increase the logic inside the IF statement.

Let's say we wanted to count "Discount %" under 2.9% and above 3.2% we could rewrite our metric with this formula: IF((([Discount %] < 0.029) Or ([Discount %] > 0.032)), 1, 0)

The metric can be used in a document with a higher level attribute and it will aggregate the values showing a total.