Using calculated KPIs and adding them to s
This guide explains what calculated KPIs are, how to manage your calculated KPIs, and how to add them to s in the Explore page.
Introduction
Calculated KPIs are metrics created based on a combination of other metrics, such as a sum or a ratio. Calculated KPIs can be used in Explore and Present to analyze and visualize your data. Adverity displays calculated KPIs in green with the sign.
Calculated KPIs can be loaded into Explore & Present but not external destinations.
Do not retrieve calculated KPIs from data sources  this can result in incorrect data. Instead, fetch the underlying metrics from your data source and use Adverity to compute the calculated KPI you need.
Creating calculated KPIs
Create calculated KPIs to perform calculations on existing metrics and add the results of the calculations to a . The example below explains how to create a calculated KPI that expresses cost per click (CPC) values. The calculated KPI created in this example is identical to the default CPC calculated KPI offered by Adverity.
Only users with the required permissions can create calculated KPIs. For more information, see Managing user permissions.
To create a calculated KPI from the administration, follow these steps:

Go to the Explore page.

In the top left corner, click Select workspace, then Administration.

In the Explore section of the administration menu, click KPIs and dimensions.

In the top right corner, click Create calculated KPI.
Or, to create a calculated KPI from Explore, follow these steps:

Go to the Explore page.

Next to the View tabs at the top of the page, click Create new view.

In the Metrics section of the view, click + Create new calculated KPI. This opens the Create a new calculated KPI screen.
To configure the new calculated KPI, follow these steps:

In the Define KPI formula section, configure the following fields:

Select metrics to add to formula below

In the dropdown list, select a metric to use in your formula, then click + Add metric. Repeat this step for all metrics you want to use in the formula.

Formula

Use mathematical operators to specify the calculation to perform using the metrics or previously created calculated KPIs you have selected. For example, use
/
to add a division sign between two metrics:(costs)/(clicks)
. 
Use Ctrl+Space to see suggestions for the formula.

Calculated formula

This field shows the full formula you have created and whether or not it is valid. If the formula is not valid, this field tells you where the syntax error is.


In the Define calculated KPI section, configure the following fields:

Calculated KPI name

Enter a name for the new calculated KPI to help you identify it. This is the name that is used for this calculated KPI in the Adverity platform.

Format

If your calculated KPI represents a percentage value, currency, or duration, select the corresponding format option.

Visibilities

Select the workspaces that can access the new calculated KPI.


Click Create calculated KPI.
As a result, you have created a new calculated KPI. You can now add this calculated KPI to s.
Adding a calculated KPI to a
To add a calculated KPI to a in the Explore page, follow these steps:

Go to the Explore page.

Next to the View tabs at the top of the page, click Create new view.

From the Dimensions & Metrics list on the left, select the calculated KPI that you want to add to your . Calculated KPIs are displayed in green with the sign.
Calculated KPIs appear in the Dimensions & Metrics list for all data sources, but the formula will only provide a correct result if the data you have fetched for the selected data source contains values for the metrics used in the calculation.
For example, if you collect Costs and Clicks data, add these metrics to your , and add the (costs)/(clicks)
calculated KPI to the , this calculated KPI will provide a correct result in the data table.
If you collect Costs and Impressions data, add these metrics to your , and add the (costs)/(clicks)
calculated KPI to the , this calculated KPI will not provide a correct calculation result for this data.
As a result, you have added the selected calculated KPI to your . You can now continue adding other dimensions and metrics to your as normal.
Editing and deleting a calculated KPI
To edit or delete an existing calculated KPI, follow these steps:

Go to the Explore page.

In the top left corner, click Select workspace, then Administration.

In the Explore section of the administration menu, click KPIs and dimensions.

In the top right corner, click Create calculated KPI.

At the bottom of the screen, click Open calculated KPI.

Select the calculated KPI you want to edit and click Apply.

Perform any of the following actions:

To edit the selected calculated KPI, make any changes to the fields in the Define KPI formula and Define calculated KPI sections. Then click Update calculated KPI.

To delete the selected calculated KPI, at the bottom of the screen, click Delete.

As a result, you have edited or deleted the selected calculated KPI.
Viewing your calculated KPIs
To view your calculated KPIs, follow these steps:

Go to the Explore page.

In the top left corner, click Select workspace, then Administration.

In the Explore section of the administration menu, click KPIs and dimensions.

At the top of the page, select the Calculated KPIs tab.
For more information, see Viewing and configuring dimensions, metrics and calculated KPIs.
Creating calculated KPIs with complex formulae
The example above explained how to create a calculated KPI where the Formula field only contains the following basic mathematical operators:

addition
+

subtraction


multiplication
*

division
/
It is possible to use more complex formulae, such as conditional statements and average calculations.
Using conditional statements in formulae
Specify the condition in the Formula field in the following way:
CASE WHEN {condition} THEN {calculation1} ELSE {calculation2} END
In the code above, replace the following:

Replace
{condition}
with a conditional statement. Use basic mathematical operators and the following:OR
,AND
,IS NULL
,NULLIF
. 
Replace
{calculation1}
with the value of the calculated KPI if the condition is satisfied. 
Replace
{calculation2}
with the value of the calculated KPI if the condition is not satisfied.
For example, if you use the formula CASE WHEN (clicks)>100 THEN (costs)/(clicks) ELSE (costs)*0.01 END
in the example above, Adverity computes the calculated KPI in the following way:

If the number of clicks for a campaign is greater than 100, the CPC value is the ratio of costs and clicks.

If the number of clicks is less or equal to 100, the CPC value is 1% of the costs.
Using dimensions in formulae
Conditions used to compute calculated KPIs can include dimensions as well as metrics.
For example, if you use the formula CASE WHEN week IS NULL AND (clicks)<100 THEN 0 ELSE 1 END
in the example above, Adverity computes the calculated KPI in the following way:

If the week dimension is null and the number of clicks for a campaign is less than 100, the calculated KPI value is set to 0.

If the week dimension is not null and/or the number of clicks is equal to or greater than 100, the calculated KPI value is set to 1.
Generally, you cannot perform calculations on the values of a dimension. One exception is that you can calculate the unique values in a dimension. To do so, use the following code: COUNT(DISTINCT({dimension}))
, replacing {dimension}
with the name of the dimension whose unique values you want to count.
For example, the code COUNT(DISTINCT(campaign_name))
calculates the number of unique campaign names.
Performing calculations on data in a table in Explore
To use the Formula field for a calculated KPI to perform a calculation on data that is shown in a table in Explore, use the following expressions:

To calculate the average of a metric's values, use
AVG({metricname})
. Replace{metricname}
with the metric's name. 
To calculate the sum of a metric's values, use
SUM({metricname})
. Replace{metricname}
with the metric's name. 
To compute a function differently for all distinct combinations of dimension values, use
OVER (PARTITION BY 0)
after the function. 
To compute a function differently for all distinct combinations of dimension values, excluding the last dimension, use
OVER (PARTITION BY 1)
after the function. 
To compute a function differently for all distinct combinations of dimension values, excluding a number of dimensions at the end of the list, use
OVER (PARTITION BY {numberofdimensionstoexclude})
after the function. Replace{numberofdimensionstoexclude}
with the number of dimensions to exclude. 
To compute a function for all rows without splitting data for any dimensions, use
OVER()
after the function.
In this example, from left to right, the following fields are represented in a table:

The dimensions Datasource and Daily.

The metric Clicks.

The calculated KPIs with the formulae
SUM(clicks)
andSUM(SUM(clicks)) OVER (PARTITION BY 0)
. In these columns, every row calculates the sums of clicks for each day and each data source. For example, it displays clicks on Facebook Ads for a particular day. The result is the same as the values in the Clicks column. 
The calculated KPI with the formula
SUM(SUM(clicks)) OVER (PARTITION BY 1)
. In this column, every row calculates the sums of clicks per data source, without splitting data by day. This is why the number of clicks per day is the same within each data source. 
The calculated KPIs with the formulae
SUM(SUM(clicks)) OVER (PARTITION BY 2)
andSUM(SUM(clicks)) OVER ()
. In these columns, every row calculates the total sum of clicks, without splitting data by data source and day. This is why the number of clicks is the same for all rows.
Use the following formula to calculate the difference from the average cost:
(costs)  AVG(SUM(costs)) OVER (PARTITION BY 1)