Aggregations (Power BI)

What are aggregations in Power BI?

Aggregations in Power BI are pre-summarised tables that sit alongside a detailed table so that common report queries are answered from the small summary instead of scanning every row. When a visual asks for something the summary already holds, like total sales by month, the engine reads the aggregation. When it needs individual rows, like the lines on one receipt, it falls back to the detailed table.

The idea is the same one a shopkeeper uses when they keep a running total at the till rather than recounting the whole drawer for every question. The detail is still there if anyone needs it, but the everyday answer comes from a much smaller number that is quick to read.

This matters most for large models that use DirectQuery, where the detailed table lives in the source database and every query is sent there live. An aggregation gives those models a fast in-memory shortcut for the queries that do not need row-level detail.

When aggregations help

1. Very large DirectQuery tables. A fact table with hundreds of millions or billions of rows is too big to import in full, but most dashboards only ask for totals by month, region, or product category. An aggregation answers those without touching the source.

2. Slow source databases. If the underlying warehouse is under load or simply not fast at analytical queries, an imported aggregation takes the everyday reporting traffic off it.

3. Mixed detail and summary reporting. Executives look at high-level trends while analysts drill into single transactions. Aggregations serve the summary fast and let the detail queries fall through to the source.

4. Reducing capacity cost. Fewer, smaller queries hitting the source means less pressure on both the database and your Power BI capacity.

How aggregations work

You define an aggregation table at a chosen grain, for example sales grouped by date, product category, and store. Each column in the aggregation is mapped either to a group-by field or to a summarised value such as a sum or a count. Power BI stores this table and uses it automatically.

The behaviour that makes aggregations useful is aggregation awareness. The report author writes normal measures and visuals; they do not point anything at the aggregation by hand. At query time the engine checks whether the aggregation can answer the request at the grain being asked. If it can, the answer comes from the summary. If the query is more detailed than the aggregation holds, the engine ignores the summary and queries the detailed table instead.

An imported aggregation added to a DirectQuery detail table turns the model into a composite model, because it now mixes storage modes. The aggregation is usually kept in Import mode, so it lives in the in-memory VertiPaq engine and returns results in a fraction of a second, while the detailed fact table stays in DirectQuery.

You can also stack more than one aggregation at different grains and give each a precedence. The engine tries the highest-precedence summary first, then a coarser one, and only reaches the detailed table when no summary fits.

User-defined and automatic aggregations

There are two ways to get aggregations. User-defined aggregations are the ones you design yourself: you pick the grain, build the table, and map every column. This gives full control and suits teams who know their query patterns and their data model well.

Automatic aggregations take a different route. They use machine learning to watch which queries run against a DirectQuery model and build and maintain an in-memory aggregation cache on their own, without you defining a grain. They are built on the same underlying mechanism as user-defined aggregations, and they retune themselves over time as query patterns change. The trade-off is less control in exchange for far less modelling work.

What to watch out for with aggregations

The summary must be much smaller than the detail. Microsoft's guidance is that an aggregation should hold at least ten times fewer rows than the table it summarises. If your detail table has a billion rows, keep the aggregation under about a hundred million. A summary that is nearly as big as the detail costs you memory and refresh time without buying much speed.

Only queries at or above the aggregation grain benefit. If you build a monthly aggregation but most reports filter by day, the engine keeps falling through to the source and the summary sits unused. Match the grain to how people actually query.

Keeping the summary in sync. An imported aggregation is a cached copy, so it is only as fresh as its last refresh. If the detail moves in near real time but the aggregation refreshes overnight, the two can disagree for a while. Plan the refresh cadence around how fresh the summary needs to be.

Hybrid tables do not support aggregations. A fact table split into import and DirectQuery partitions cannot also carry an aggregation, so choose one approach per table rather than trying to combine them.