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 Reporting & Dashboards 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.


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:

  1. 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 .
  2. In the Dataset editor, you can create both measures and dimensions of “string” type. On the chart editor, you can create calculated measures only.
  3. 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.

Let's say you have a dataset like this:

Small dataset example

If you create the formula m3 = m2/m1 on the Dataset editor , it first counts new values:

Dataset editor example
            of order of calculations

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:

Chart editor example
            of order of calculations

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.

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 Reporting & Dashboards 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).

Adding a pre-built
    calculation to a chart

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


Difference from Previous

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

Difference from Previous example

Percent from Previous

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

Percent from Previous example

Percent Difference from Previous

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

Percent Difference from Previous
    example

Percent of Total

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

Percent from Total example

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.

Running Total example

Parallel Period 7

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

Parallel Period example

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-on-year example

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).