# Measures, Dimensions & Calculations

On this page you'll learn what are **measures**, **dimensions**,**
calculations** and ** pre-built calculations**, and how to use them to empower your graphs
and
charts made in the Data Visualization Tool

### Measures

Measures are quantitative values from a dataset. These are the numeric values that you want to analyze when
visualizing your data. The column types from your SlicingDice database that generate this kind of data can be
`integer`

, `integer-event`

, ```
decimal
```

and `decimal-event`

. Some examples of these values are: **sales
revenue,
profit, age, number of website visits, costs** and so on.

### Dimensions

Dimensios are qualitative values from a dataset. These are the categories by which you group your measures. The
column types from your SlicingDice database that generate this kind of data can be `string`

, `string-event`

, `boolean`

, ```
date
```

, `datetime`

, `geolocation`

and `geolocation-event`

. Some examples of these values are: **location, company name, product category,
country, date** and so on.

Dimensions mentioned anywhere inside the Data Visualization Tool are not the same as dimensions from a database. They are conceptually very different and should not be mistaken.

### Calculations

Calculations are mathematical formulas applied on measures, in order to present a refined result on your charts.
This provides you the freedom to create the measures and dimensions you need to visualize. Some examples of
these
values are: **[Total number of sales] - [Total number of returns]**, **[Total revenue] -
[Total
operations cost]** and so on.

You are able to create fields by defining a formula that is based on existing fields and other calculated fields,
using standard funcionts and operators. This can be achieved in two ways: via **Dataset editor**
(using
SQL) or via **Chart editor** (using MDX).

#### Difference in Dataset editor and Chart editor calculations

Although you can choose either way to perform your calculations, there are some differences between them:

- Calculations made on the Dataset editor are available for
**all charts built on this dataset**. Calculations made on the Chart editor are available for the charts that are**on the current dashboard**. - In the Dataset editor, you can create both measures and dimensions of “string” type. On the chart editor, you can create calculated measures only.
- There is also a difference in order of calculations and aggregations in these two methods:
- On the Dataset editor, the column is first calculated according to the formula, then the measures are aggregated.
- On the Chart editor, measures in the formula are aggregated, then the formula is calculated.

##### Example the order of calculations difference of Dataset and Chart editor calculations

Let's say you have a dataset like this:

If you create the formula `m3 = m2/m1`

on the **Dataset editor
**, it first counts new values:

And then, if you create a Single value chart, the system aggregates them: ```
m3
=
5 + 6 + 2 = 13
```

.

Using the same formula, `m3 = m2/m1`

, on the **Chart editor
**, it first aggregates the fields:

And then it calculates the formula: `m3 = m2/m1 = 4.6`

.

#### Via Dataset Editor (SQL)

On the **Dataset editor** page, click on *Add calculation* button at the top right
corner. A
new screen will be shown, where you can name your calculation and insert the desired formula in the **Formula
** box, using standard SQL functions.

Calculations will return the result for each row of the selected column (e.g. Abs) or return a single value, calculated from values in a column (e.g. Max), depending on the used function.

After you’ve finished the formula creation, it will be available in the list of the fields in the dataset. The field type (measure/string) will be defined automatically. For string fields, you can change the type to Geography.

You can edit the calculation by clicking on its *name*, or delete it selecting the three dots button
at the
end of the field row and click *Delete*.

You can also change the field format / visibility by clicking on *Settings* on the same three dots
button.

The calculated field will be available on the chart editor everytime you use this dataset as your data source.

#### Via Chart Editor

On the **Chart editor** page, click on *Add calculation* button to create a calculated
measure. A
new screen will be shown, where you can see all of your measures and dimensions, and the functions available
with their
their respective descriptions. This enables you to quick glance at all the variables and what the functions
returns
without having to consult anywhere else.

** Create a unique name for your measure**

**do not**allow to use the following names (including the index at the end, such as 'Fixed1') for your calculated measures:

**Left****Top****Fixed**

To learn about MDX functions, please refer to the official pages .

##### Examples

**Sum Function**

If you just want to sum up two measures together like [New Users] and [Retained Users], you can just use a
plus sign.
The **Sum** function, however, comes in handy when you want to create a new metric for a
subsection of a
measure. The form looks like this:

`Sum(Set Expression,Numeric Expression)`

So, let’s say you wanted the revenue that only comes from a certain plan you offer. It would look something like this:

`Sum([Plan.Hierarchy].[Plan].[1],[Revenue])`

The **Sum** function just has two parts. The first is the member that you want it to use and the
second
is the measure you want it to use.

If you want to have several plans, this is when you would use a Set in place of where we have [Plan.Hierarchy].[Plan].[1]. A set is a list of members all from one hierarchy. A set uses curly brackets { } and each member is separated by a comma.

```
Sum({[Plan.Hierarchy].[Plan].[1],[Plan.Hierarchy].[Plan].[2],[Plan.Hierarchy].[Plan].[3]},[Revenue])
```

Now because the first part of the **Sum** function just needs a set, you can put a whole bunch
of other
functions in there including the **Filter** and **Except** functions. As long as
the function
returns a set, you can put these functions inside the first part of the **Sum** function.

**Filter Function**

The **Filter** function is super useful and can be used in conjunction with the
**Sum**
function. This is because the **Filter** function returns a set of members that all that pass
some
logical test. The filter function will look like this:

`Filter(Set Expression, Logical Test)`

Insert a hierarchy in the first part and insert .Members afterward. This just says, “Hey, I want you to look at all of the members in this hierarchy and see if it passes my test.” Then you input a test. The following example returns a set of customers that all made a payment the previous member amounting to more than 0.

```
Filter([Customer.Hierarchy].Members,aggregate([Date.Hierarchy].[Month].Currentmember.Prevmember,[Revenue])>0)
```

**Count(Set) Function**

The **Set** function counts the number of members in a set. The function will look like this:

`Count(Set Expression)`

A great application of the **Set** function is calculating Average Revenue per Paying Customer
(ARPPU).
Since you already made a filter function above, you can modify it to see if the customer made a payment in
the
Currentmember by removing the .Prevmember.

Combine the count function with the formula above and you will get the number of customers that made a payment in the Currentmember.

```
Count(Filter([Customer.Hierarchy].Members,aggregate([Date.Hierarchy].[Month].Currentmember,[Revenue])>0)
```

Once you have this, divide your total Revenue measure by this function to return your ARPPU.

```
[Monthly Recurring
Revenue]/Count(Filter([Customer.Hierarchy].Members,aggregate([Date.Hierarchy].[Month].Currentmember,[Revenue])>0)
```

### Pre-built Calculations

Pre-built calculations enable you to present some tendencies in your data without creating new calculated
measures
manually. This special type of measure is available **only** to the following chart types: Bar
chart, Time
series, Combo chart and Cross-table.

#### Adding pre-built Calculations

Inside the Data Visualization Tool, on the chart creation/edition page, on the **Layout** tab
click on
*+ ADD MEASURE* and choose **Calculations** at the bottom of the list of existing
measures. You'll
be presented with the pre-built calculations list. Select the one that suits you best, choose a measure and
select the
aggregation (ir required).

There are 5 pre-built calculations available for all types of dimensions, and 6 additional options for Date dimensions:

- Difference from Previous
- Percent from Previous
- Percent Difference from Previous
- Percent of Total
- Percent of Selected Elements
**[Date only]**Running Total**[Date only]**Parallel Period 7**[Date only]**Parallel Period 14**[Date only]**Parallel Period 28**[Date only]**Year-on-year**[Date only]**Year-over-year

#### Difference from Previous

Shows the difference between the current value and the previous one.

#### Percent from Previous

Shows the percentage difference between the current value and the previous one (absolute value).

#### Percent Difference from Previous

Almost the same as Percent from Previous, but it represents an increment of growth and can contain negative values.

#### Percent of Total

Shows the share of the current value from the total (total = sum of all the values).

#### Percent of Selected Elements

Almost the same as Percent of Total, but when you add a filter, it recalculates the total value and shows the shares of represented values from a total of these values.

#### Running Total

At each step, it sums up the current value and the previous ones and thus represents cumulative sum.

#### Parallel Period 7

Compares the selected period which has 7 steps (days, months, quarters, or years) with a similar previous period.

#### Parallel Period 14

Compares the selected period which has 14 steps (days, months, quarters, or years) with a similar previous period.

#### Parallel Period 28

Compares the selected period which has 28 steps (days, months, quarters, or years) with a similar previous period.

#### Year-on-year

Compares data for a selected period with the same period 1 year ago.

#### Year-over-year (YoY comparison)

Compares data for the selected period with the same period 1 year ago (data this year/data a year ago-1).