9. Manage aggregation

While working with Power BI table/matrix reports, we would sometimes like to apply custom aggregations for the measures in a specific report (e.g., average quantity sold) instead of using the default aggregation used in a data model (e.g., the sum of quantity sold).

Premium Table’s aggregation feature allows you to override the native Power BI aggregation for measures without having to make extensive model-level changes. You can set aggregation at the row grand totals.

Click the Aggregation button in the Insert ribbon to open the Manage Aggregation interface.

Manage Aggregation

• For PY Sales, Actual Sales, and Forecast, the sum is set as row aggregation. The row grand total for these measures will be calculated as the sum of all the rows.

Aggregation types

None

Choosing 'None' performs no aggregations for the selected measure. In the example below, the 'Discount' measure is not aggregated.

Row aggregation is 'None'

Sum

'Sum' aggregation displays the sum of the child rows as the aggregate. In the example below, the Actual Sales column is the sum of its child rows.

Row aggregation is 'Sum'

Minimum

The 'Minimum' aggregation shows the minimum value of all rows as the aggregate. The aggregation for the Actual Sales measure is set to 'Minimum', so it displays the lowest value in that measure.

Row aggregation is 'Minimum'

Maximum

The 'Maximum' aggregation shows the maximum value of all rows as the aggregate. The aggregation for the Actual Sales measure is set to 'Maximum', so it displays the highest value in that measure.

Row aggregation is 'Maximum'

Average

The 'Average' aggregation shows the average of the rows as a row aggregation. In the example below, when the aggregation is set to 'Average', the average sales is calculated at the grand total level.

Row aggregation is 'Average'

Standard deviation

'Standard deviation' aggregation displays the standard deviation of the child rows as the aggregate.

Standard Deviation as aggregation type

Visible rounding

'Visible rounding' aggregation rounds off values in a way that the individual row values add up properly to the grand total. This is a common requirement in external financial statement reporting, including income and balance sheet reporting.

Visible rounding

First

This aggregation type is often used in time-series data or any sequential dataset. The 'First' aggregation type displays the first value from the set of rows. This is especially useful when we need to record the dataset's initial state or value.

'First' aggregation

Last

This aggregation type is used in time-series data or any sequential dataset. The 'Last' aggregation type displays the last value or the latest value from the set of rows. This is useful when we need the most recent value in a dataset, especially when budgeting for the following year by carrying over the previous year's value.

'Last' aggregation