Search Results

Using OVER statements in calculated columns

You can perform calculations on your data, and display the results as values in a new column that is added to the data table. In the expression used for the calculation, you can use an OVER statement. However, using the OVER statement in calculated columns works differently from using it in custom expressions. In calculated columns, the OVER expression is used to calculate separate, fixed, values for different groups and the OVER expression might cause the data to be 'sliced into fewer records'.

Note: Calculated columns are always calculated on all values in the column, no matter how you filter your data.

When you work with calculated columns, there are no axes available to navigate over, as in the case with custom expressions. Instead, you use the OVER expression to refer to a data column (or hierarchy).

In the data table below, the expression

Sum([Sales]) OVER ([Fruit])

is used for calculations of the values in the added column:

That is, for each row, the total sum of sales for the fruit type in question is presented in the new column. For example, as the total sales of apples is 200 (30+70+100), this value is displayed on every 'Apples' row. The effect is slicing into fewer values (200 and 100).
Note: The new column values will not change when data is filtered out afterwards. For example, if the data row at the top is filtered out, the calculated column values for the 'Apples' rows will still be 200.

Extended example

The calculated column with fewer records may not be very useful as a standalone column. However, let us add another column, where the previous expression is a part of the expression used:

[Sales] / (Sum([Sales]) OVER ([Fruit]))

This expression calculates each sales transaction's relative contribution to the total sales over the years within its fruit type. For example, 15% (30/200) of the total apples sales was in 2015, 35% (70/200) in 2016, and the rest, 50% (100/200), in 2017.

In the bar chart below, the calculated column is selected on the Value axis using the Sum aggregation.

In fact, this result is the same as using the 100% stacked bars option in a bar chart showing Sum([Sales]).

Combining OVER with a node navigation method in a calculated column

In the expression used for calculating a column, you can use the OVER statement in combination with a node navigation method. This simple example gives you an idea how to use one of the methods, Previous().

The data table lists weekly measured weights for a newborn child, visualized in a line chart.

Assume you are interested in the weekly growth. This can be calculated using the expression:

[Weight (g)] - Sum([Weight (g)]) OVER (Previous([Week]))

The results can be viewed in the data table and bar chart below.