Aggregations at an intermediate level

A dashboard can display aggregated data at different levels, by specifying the dimensions on which you want to aggregate.

Introduction

The aggr() function allows to perform an aggregation according to the chosen function on a set of predefined dimensions. This function can be used in calculated fields. The first argument is a summary expression calculated with respect to a specific data source field. The next arguments are the dimensions that define the calculation level.

aggr(SummaryExpression, Dimension1, Dimension2, ...)

For example, the following function calculates the annual product sales:

aggr(Sum([Sales]),[Product],GetYear([Date]))

The first argument of the function is the summary expression calculated on the specified source field. The following arguments represent a set of dimensions that will be used to calculate the aggregations using the function specified in the first argument. For example the following function calculates the sum of sales for each product category, and returns this result even if the displayed data is more detailed.

We create the following function Sales Amount per category:

aggr(Sum([Sales Amount]), [Category]) 

We have a grid with the fields Category, Subcategory and Sales Amount. The amount displayed is the amount per subcategory and not per category.

We add the previously calculated field Sales Amount per category to the grid and we obtain :

More detailed granularity

To aggregate the data by category and subcategory, you can create the calculated field "Amount by subcategory" with the following expression:

Aggr(Sum([Sales Amount]), [Category],[Subcategory]) 

The next grid of the dashboard presents data less detailed than the calculated field: category and sales amount.

If you add the previous calculated field, with the Min aggregation function, you get the following result:

The grid shows you the sales of the subcategory with the lowest revenue for each category.