How to Create an Aggregate Table in Power BI

If your Power BI reports are starting to feel slow and sluggish, you’re not alone. As datasets grow from thousands to millions of rows, dashboards that once loaded in a snap now take forever to refresh their visuals. Before you start looking for more powerful hardware, there’s a much smarter fix: creating an aggregate table. This article will show you exactly how to build and use aggregated data in Power BI to significantly speed up your reports and simplify your data model.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is an Aggregate Table and Why Should You Use One?

Think of your main data table like a detailed list of every single sale your company has ever made - every individual transaction, down to the second it happened. This is called a "detail" or "fact" table, and it's full of valuable information, but it can be massive.

An aggregate table is simply a summary of that detailed list. Instead of listing every single sale, it might show the total sales per day, per product category, or per marketing campaign. It takes the highly granular data and rolls it up into a much smaller, more manageable table. For example, a table with 10 million individual sales records could be summarized into an aggregate table with just a few thousand rows showing monthly totals.

So, why would you do this? The benefits are simple but powerful:

• Drastically Improved Performance: This is the main reason. Power BI visuals that query an aggregate table of 50,000 rows will load exponentially faster than visuals querying a detail table with 50 million rows. Your slicers will be more responsive, charts will appear instantly, and your user experience will improve dramatically.
• A Simpler Data Model: For high-level overviews and dashboards, working with a summarized table is often much easier. It presents the data at the right level of detail for executive summaries or trend analysis, making your model cleaner and more intuitive to navigate.
• Reduced Resource Consumption: Smaller summarized tables use less memory (RAM) and processing power when being queried, which is especially important if you are using Power BI Pro capacities or shared resources.

You should consider creating an aggregate table whenever you find yourself working with very large datasets (typically over a million rows) that are causing your reports to perform poorly, or when most of your report visuals only need to display summarized data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Create an Aggregate Table Using Power Query

The most straightforward way to build an aggregate table is directly within the Power Query Editor. It’s a visual process that doesn’t require writing any code. Let's walk through it using a common example: summing up daily sales data to get a monthly summary by product category.

Step 1: Duplicate Your Primary Detail Table

First, open the Power Query Editor by clicking "Transform data" on the Home ribbon in Power BI Desktop.

In the Queries pane on the left, find your large fact table (e.g., "SalesData"). Right-click on it and select "Duplicate". This creates a new, separate query to work from, leaving your original data untouched.

It's good practice to immediately rename this new query to something clear and descriptive. Right-click on the duplicate and rename it to something like "Sales_Agg_Monthly" or "MonthlySalesSummary".

Step 2: Start the Grouping Process

With your new aggregate query selected, go to the "Home" or "Transform" tab on the Power Query ribbon and click on the "Group By" button. This opens a dialog box that will do all the heavy lifting for you.

Step 3: Define Your Groups and Aggregations

The "Group By" dialog is where you tell Power Query how to summarize your data. Let's say our goal is to see total sales and order counts for each product category on a monthly basis.

• You'll likely start in the "Basic" view. To group by multiple columns (like Month and Category), switch to the "Advanced" radio button.
• Under "Group by", select the columns that define your new summary level. For our example, you would add the Product_Category column and a column representing the month (you might need to create this first by selecting your Date column and using "Add Column" > "Date" > "Month" > "Start of Month").
• Now, define your aggregations under the "New column name" section.

Once configured, your dialog box should look like it's ready to group everything by month and category, calculating the total sales and order count for each combination.

Click "OK" to apply the transformation. You'll see your millions of rows instantly shrink down to a compact, summarized table.

Step 4: Close and Apply

Finally, click "Close & Apply" in the top-left corner of the Power Query Editor. Power BI will load your new aggregate table into the data model alongside your original detail table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Create an Aggregate Table with DAX

If you're more comfortable with writing formulas, you can achieve the same result using DAX (Data Analysis Expressions). This is done in the main Power BI window, not the Power Query Editor, and creates a calculated table.

The most common DAX function for this is SUMMARIZE.

1. Navigate to the "Data" view or "Report" view in Power BI Desktop.
2. From the "Table tools" ribbon menu, click "New table". This opens a formula bar.
3. Now, write your SUMMARIZE expression. The syntax is:

Here’s how our example would look in DAX:

MonthlySalesSummary_DAX = 
SUMMARIZE(
    SalesData,
    'Calendar'[Start of Month],
    'Products'[Product_Category],
    "TotalSales", SUM(SalesData[Sales_Amount]),
    "OrderCount", DISTINCTCOUNT(SalesData[Order_ID])
)

In this formula:

• SalesData is our original detail table.
• 'Calendar'[Start of Month] and 'Products'[Product_Category] are the columns we're grouping by. It's best practice to use columns from your dimension tables (like a Calendar table and Products table) here.
• "TotalSales" is the name of our new calculated column, holding the result of SUM(SalesData[Sales_Amount]).
• "OrderCount" is the second new column, holding the result of DISTINCTCOUNT(SalesData[Order_ID]).

Press Enter, and Power BI will generate the new aggregate table in your model instantly.

How to Configure Aggregations for a Huge Performance Gain

Just creating the table is only half the battle. To unlock the real performance benefit, you need to tell Power BI about the relationship between your new aggregate table and your original detail table. This lets Power BI's engine automatically use the small summary table for high-level queries and only tap into the massive detail table when a user drills down to see individual transactions.

This setup is known as managing aggregations.

Step 1: Set Up Your Relationships

First, go to the "Model" view in Power BI. Confirm that your new aggregate table is related to your dimension tables on the same columns that your detail table is. For instance, link MonthlySalesSummary[Start of Month] to 'Calendar'[Start of Month], and MonthlySalesSummary[Product_Category] to 'Products'[Product_Category]. The relationships should mirror what your large fact table uses.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Manage Aggregations

In the Model view, right-click on your new aggregate table (e.g., MonthlySalesSummary) and select "Manage aggregations".

A dialog box will appear. Here, you will map the columns from your aggregate table to the columns in your detail table. For each column in your aggregation table:

• First Column (e.g., Start of Month): For the grouping columns, for the "Summarization" setting, choose "GroupBy". This tells Power BI that the Start of Month column in your aggregate table corresponds directly to the Start of Month column from your dimension table. You'll do the same for the Product_Category column.
• Subsequent Columns (e.g., TotalSales):
• For other metrics like OrderCount, do similarly, choosing the appropriate aggregation functions and columns.

After clicking "Apply all", your setup is complete. Power BI now understands that MonthlySalesSummary is a clean, pre-calculated aggregation of SalesData.

Best Practices for Aggregate Tables

To get the most out of this technique, keep a few final pointers in mind:

• Choose the Right "Grain": The "grain" refers to the level of detail. Don't aggregate too much or too little. The goal is a significant reduction in row count while still providing the level of insight needed for the majority of your visuals.
• Verify Your Numbers: Before you trust your new table completely, create a simple card visual in your report. Drag the Sales_Amount from your detail table into one card, and the TotalSales from your aggregate table into another. The grand totals should match perfectly. If they don't, check your grouping or filtering logic in Power Query.
• Hide the Detail Table (Optional): Once the aggregations are set up, you can right-click your original, multi-million-row detail table in the Fields pane and select "Hide from report view". This prevents business users from accidentally trying to use it in visuals, forcing queries to route through the faster aggregation logic. The detail table is still available for drill-through actions.
• Use a Calendar Table: You’ll get the most out of aggregations when you combine them with a properly configured data model that includes a dedicated calendar table and other dimension tables.

Final Thoughts

Creating and configuring aggregate tables is one of the most effective ways to boost the performance of your Power BI reports when dealing with large volumes of data. By summarizing your data ahead of time, you give the report engine a much smaller, faster dataset to work with for most user interactions, leading to a snappier and more pleasant analytics experience.

Preparing data models, setting up relationships, and managing aggregations in tools like Power BI is incredibly powerful, but it involves a significant amount of "under the hood" work that takes time and technical know-how. At Graphed, we focus on removing that complexity entirely. We've built an AI data analyst that allows you to connect your data sources - whether they're simple spreadsheets or massive databases - and build real-time dashboards just by describing what you want to see in plain English. No more manually building summary tables or configuring query logic, the aggregation and data modeling happens instantly, all in the background, so you can go from data to insight in seconds. Get started with Graphed today.