Insights - Creating Formulas Based on Criteria and Conditions (Filters)

Updated 2 years ago by Elena Yurkova

Creating Formulas Based on Criteria and Conditions (Filters)

Often formulas must take into account specific criteria. In Insights there is a feature called Measured Value, which is similar to the SUMIF function in Excel, and only performs a calculation when the values meet a set of criteria.

Criteria for Measured Values may be based on any logical operators in a filter.

Measured value

Measured Values are a powerful feature that take into account business logic and quickly perform calculations only when a specific set of criteria is met.

Note: If your widget is filtered using measured values, then the measured value will override any other widget or dashboard filters you have for the same fields.
  1. Create your formula from the Data Browser and Functions, as explained in Insights - Formula editor.
If you only worked with simple formulas, they might look like this COUNT[Identification] or SUM[Revenue]. To create formulas with multiple arguments, learn how to bring function inside the field.To do that, simply click on the field to change type to 'Count Unique':
  • Use the count function for whatever is inside the []. Remove letters COUNT from the outside so your formula looks like this:
  1. Add the field (criteria) by which you want to filter the formula. Right-click the field and select Filter.
  2. Filter the formula if needed. When done, click OK.
  3. Put round brackets around your formula to complete the formula

The syntax below works too for this case, but if you use multi-select fields you might get duplicates, thus formula above is more reliable.

A simple example of Measured Value is the use of a list filter. A marketing team may need to count leads generated for a specific region such as North America. Even if leads come from many different countries, the measured value calculates leads generated only when the lead originates from the United States or Canada.

A more sophisticated case is the use of a ranking filter. For example, a sales team may want to track the contribution of best-selling products to total revenue. However, what constitutes a popular product may change over time. A measured value can be created for sales which includes a condition that only shows sales for the top products for any month. This simultaneously filters the data but also takes into account changes in what classifies as a top product over time.

The above example as defined in the Formula Editor.

Calculating Contributions Using the ALL Function

The All() function returns the total amount for a dimension, and can be used for various use cases. In the following example, we will use the All function to calculate how much each country contributed towards the total cost of a campaign.

Our final widget includes the following information:

  1. The second column above represents a formula that sums up the total cost for all countries, but not the breakdown per country. The formula includes the calculation (total cost) followed by the all function (filter), followed by the dimension (country) in parenthesis. It looks like this:

We can save (star) the above formula and call it Total Cost for Countries, which will be used in the next step.

  1. We can now use the above formula in another formula to calculate the contribution, like this:

The result is the third column above (plus formatting the results as percentages).

How did we do?