Aggregations for performance in Power BI - Step-by-Step Guide

Introduction

Aggregations help your Power BI reports run faster by summarizing large data tables into smaller, simpler tables. This reduces the amount of data Power BI needs to process when you interact with visuals, making your dashboards quicker and smoother.

When your sales report slows down because it uses millions of detailed transaction rows

When you want to speed up a dashboard that shows monthly totals instead of daily details

When your data model has large fact tables and you only need summary numbers for some reports

When you want to reduce the load on your data source by pre-calculating totals

When you want to improve user experience by making visuals respond instantly

Steps

Step 1: Open

- Power BI Desktop

Your report and data model are visible

Step 2: Go to

- Model view

You see all tables and their relationships

Step 3: Click

- Modeling tab > Manage aggregations

The Aggregations dialog opens

Step 4: Select

- A table in the Aggregations dialog

You can define aggregation rules for columns

Step 5: For each column, choose

- Aggregation type dropdown

Aggregation types like Sum, Average, Min, Max are set

Step 6: Save

- Aggregation settings

Power BI uses these aggregations to speed up queries

Step 7: Test

- Report view by interacting with visuals

Visuals load faster using aggregated data

Before vs After

Before

Report uses a large sales transactions table with 5 million rows; visuals take 10 seconds to load

After

Report uses an aggregation table with monthly sales totals; visuals load in 2 seconds

Settings Reference

Aggregation type

📍 Manage aggregations dialog for each column

Defines how the column data is summarized in the aggregation table

Default: None

Aggregation table

📍 Model view > Table properties

Specifies which table holds the aggregated data

Default: No aggregation table

Enable aggregations

📍 Model settings

Turns aggregation feature on or off for the model

Default: Off

Common Mistakes

Not creating a proper aggregation table with matching columns

Power BI cannot use aggregations if the summary table columns do not match the detailed table columns

Create an aggregation table with the same column names and data types as the detailed table columns you want to aggregate

Setting aggregation type to None for columns used in visuals

Power BI will not use aggregation for those columns, causing slow queries

Set appropriate aggregation types like Sum or Average for columns used in visuals

Forgetting to enable aggregations in model settings

Aggregations will not be applied even if defined

Turn on aggregations in the model settings to activate the feature

Summary

Aggregations create smaller summary tables to speed up report performance.

You must define aggregation types for columns and link them to detailed tables.

Ensure aggregation tables match detailed tables in structure and enable aggregations in settings.