Calculated metrics
This guide explains what calculated metrics are and how to use them in Adverity.
Introduction
Calculated metrics are metrics created based on a combination of other metrics, such as a sum or a ratio. Calculated metrics can be used in Data Explorer and Dashboards to analyze and visualize your data. Adverity displays calculated metrics in green with the sign.
Calculated metrics can be loaded into Adverity Data Storage but not external destinations.
Do not retrieve calculated metrics 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 metric you need.
Viewing your calculated metrics
To view your calculated metrics, follow these steps:

In the Data Dictionary.
, click 
Edit the page URL in the following way:

Remove
/dataschema
from the end of the URL. 
Add
/calculatedmetrics
to the end of the URL.
The final URL should end in the following way
.../administration/calculatedmetrics/
. 
The table in the middle of the page contains important information about your calculated metrics in the following columns:

Name  the display name of the calculated metric

Format  the type of data that the calculated metric represents

Formula  the formula used to calculate this calculated metric

Usable in  the number of workspaces in which this calculated metric can be used. Click the link in this column to see a list of these workspaces
Creating a calculated metric
Create calculated metrics to perform calculations on existing metrics and add the results of the calculations to a . The example below explains how to create a calculated metric that expresses cost per click (CPC) values. The calculated metric created in this example is identical to the default CPC calculated metric offered by Adverity.
Only users with the required permissions can create calculated metrics. For more information, see Managing user permissions.
To create a calculated metric, follow these steps:

In the Data Dictionary.
, click 
Edit the page URL in the following way:

Remove
/dataschema
from the end of the URL. 
Add
/calculatedmetrics
to the end of the URL.
The final URL should end in the following way
.../administration/calculatedmetrics/
. 

In the top right corner, click + Create calculated metric.

In the Display name field, enter a name for the new calculated metric to help you identify it. This is the name that will appear in the Data Dictionary, Data Explorer and Dashboards pages in the Adverity platform.

In the Select metrics to add to formula below field, select a metric to use in the formula for this calculated metric. It will appear in the Formula field below.

Repeat step 5 for all metrics you want to use in this formula.

In the Formula field, use mathematical operators to specify the calculation you want to perform on the metrics you have selected. Adverity will automatically check the formula and display a message stating whether the formula is valid. If the formula is not valid, this message will state where the error is in the formula.
For help writing complex formulae, see Creating calculated metrics with complex formulae.

In the Format field, select the format of your calculated metric. This depends on the value your calculated metric represents.

Number  the calculated metric represents a number, e.g. total likes on a social media post

Percent  the calculated metric represents a percent value, e.g. an interest rate

Duration  the calculated metric represents a period of time, e.g. the duration of a campaign

Currency  the calculated metric represents a monetary value, e.g. costs


In the Fractional digits field, specify how many decimal places this calculated metric will include. This field is only required for the Number, Percent and Currency formats.

In the Currency field, select the currency in which this calculated metric will be displayed. This option is only required for the Currency format.

In the Default total measure field, specify the mathematical function Adverity uses to calculate the total row for the metric:

Sum  the total row will contain the sum of all values for this calculated metric (e.g. total cost per click)

Avg  the total row will contain the average of all values for this calculated metric (e.g. average cost per click)

Count  the total row will contain the number of values for this calculated metric (e.g. total number of cost per click values that have been calculated)

Min  the total row will contain the minimum value for calculated metric (e.g. lowest cost per click value)

Max  the total row will contain the maximum value for this calculated metric (e.g. highest cost per click value)

None  Adverity will not calculate a total row for this calculated metric


Click Create.
As a result, you have created a new calculated metric. You can now add this calculated metric to s.
Creating calculated metrics with complex formulae
The example above explained how to create a calculated metric 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 metric if the condition is satisfied. 
Replace
{calculation2}
with the value of the calculated metric 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 metric 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 metrics can include dimensions as well as metrics.
For example, if you use the formula CASE WHEN dimensions>'week' IS NULL AND (clicks)<100 THEN 0 ELSE 1 END
in the example above, Adverity computes the calculated metric in the following way:

If the week dimension is null and the number of clicks for a campaign is less than 100, the calculated metric 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 metric 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.
Editing and deleting calculated metrics
Only users with the required permissions can edit and delete calculated metrics. For more information, see Managing user permissions.
Editing a calculated metric
To edit a calculated metric, follow these steps:

Select the workspace you work with in Adverity and then, in the , click Data Dictionary.

Edit the page URL in the following way:

Remove
/dataschema
from the end of the URL. 
Add
/calculatedmetrics
to the end of the URL.
The final URL should end in the following way
.../administration/calculatedmetrics/
. 

In the row for the calculated metric you want to edit, click Edit.

In the Edit calculated metric window, make the required changes. You cannot change the Technical name of a calculated metric after it has been created. You can change all other fields.

Click Save.
As a result, you have edited a calculated metric.
Deleting a calculated metric
To delete a calculated metric, follow these steps:

Select the workspace you work with in Adverity and then, in the , click Data Dictionary.

Edit the page URL in the following way:

Remove
/dataschema
from the end of the URL. 
Add
/calculatedmetrics
to the end of the URL.
The final URL should end in the following way
.../administration/calculatedmetrics/
. 

In the row for the calculated metric you want to edit, click Delete.

In the Delete window, you will see all s and calculated metrics in which this calculated metric is used.
If the calculated metric is not used in any s or calculated metrics, you can delete the calculated metric.
If the calculated metric is used in any s or calculated metrics, you must perform one of the following actions for each or calculated metric:

Remove the calculated metric from the listed or calculated metric

Delete the listed calculated metric
or


Click Delete.
As a result, you have deleted a calculated metric.
Performing calculations on data in a table in Data Explorer
To use the Formula field for a calculated metric to perform a calculation on data that is shown in a table in Data Explorer, 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 metrics 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 metric 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 metrics 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)
Video guide: How to use calculated metrics in Adverity
This video guide explains what calculated metrics are and how to create and use them in the Data Dictionary and Data Explorer.