Change & manage aggregation in Power BI matrix

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

Many times, changing the aggregation in the data model may not be possible for several reasons such as the following

• Multiple Power BI reports may be consuming the same data model, and hence the model-level measure aggregates cannot be changed 
• The report developer may not be having modeling rights to the database objects while using DirectQuery.

Change aggregations inside the matrix

Inforiver’s aggregation feature allows you to override the standard measure aggregation for specific measures in Power BI table/matrix reports. You can apply custom measure aggregations such as sum, average, standard deviation, minimum, maximum, and more and apply them to your Power BI table / matrix style visuals using Inforiver. This allows you to build reports rapidly without having to make extensive model level changes.

Inforiver offers an additional aggregation type available called 'Visible rounding'. This rounds off values in a way that the individual values add up properly to the subtotal and grand total. This is a very common requirement in external financial statement reporting such as income statement and balance sheet reporting.

In general, to change the aggregation type of a measure at the visual level, select a column from the report and change the corresponding aggregation type.

Manage aggregations for all measures

To manage aggregations for all measures at once, click on 'Manage Aggregation' from the same dropdown. The following list of measures opens up in a pop up window. You can change aggregation types of multiple measures here. Note that this window can also be invoked from the 'Manage Columns' menu at the top of the ribbon menu (you need to click on the gear icon from the dropdown).

While managing aggregates at the visual level is a great feature for specific scenarios, one needs to use this carefully. These aggregates work only on the data that is available to the visual. They do not have access to all the granular data that is present in the Power BI data model. You will need to ensure that the visual has enough context & granularity for your aggregates to be meaningful.

Here is an example where there are two versions of a report. The one on the left uses 'Sum' aggregation, whereas the one on the right uses the 'Average (Leaf)' aggregation. i.e. the grand total row 'All' averages all the twelve (12) categories.

Related Power BI Ideas:  Select if the total row should give a sum or an average for every field 

Learn more about Inforiver's reporting capabilities here.