How to Create a Summarize Table in Power BI

Wrangling large datasets in Power BI can sometimes feel like trying to sip from a firehose. When you only need to see a high-level overview, working with millions of rows of raw data can slow down your reports and overcomplicate your analysis. This is where creating a summarize table becomes an indispensable skill. In this tutorial, we will walk through exactly how to use DAX functions to create compact, aggregated tables that make your Power BI reports faster and your calculations much simpler.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is a Summarize Table?

Think of a summarize table as a condensed, "CliffsNotes" version of a larger data table. Instead of holding every single transaction or event, a summarize table holds the results of an aggregation - like total sales, average costs, or user counts - grouped by specific categories you define.

For example, you might have a massive sales table with ten million rows, detailing every single product sold. A summarize table could distill this down to just a few dozen rows showing total sales for each product category in each region.

So, why would you do this?

• Better Performance: Visuals and calculations run much faster when they query a small, pre-aggregated table instead of scanning millions of rows of a detailed one every time.
• Simplified Data Model: It can help clean up your data model by creating smaller, more manageable tables for specific reporting needs.
• Easier Calculations: It can serve as an intermediate step to make complex DAX measures easier to write and debug down the line.

You create these tables using Data Analysis Expressions (DAX), Power BI's formula language. Let's look at the primary function for this task: SUMMARIZE.

Understanding the SUMMARIZE() Function in DAX

The SUMMARIZE() function is the traditional workhorse for creating summary tables in Power BI. Its job is to take a table, group it by one or more columns you specify, and then add new columns with aggregated calculations.

The basic syntax looks like this:

SUMMARIZE(<,table>, <,groupBy_columnName1>, [<,groupBy_columnName2>], …, "<,newColumnName>", <,expression>)

This might look intimidating, but it breaks down pretty simply:

• <,table>: The original, large table you want to summarize (e.g., 'SalesData').
• <,groupBy_columnName>: The column(s) you want to use for grouping (e.g., 'SalesData'[Region], 'SalesData'[ProductCategory]).
• "<,newColumnName>": A text string for the name of your new calculated column (e.g., "Total Sales").
• <,expression>: The DAX aggregation you want to perform (e.g., SUM('SalesData'[Revenue])).

The best way to get it is to see it in action.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step-by-Step: Creating Your First Summarize Table

Let's imagine you're a marketer for an e-commerce store. You have a huge table called Sales that contains every single transaction. The table includes columns like OrderDate, Region, Product Category, Units Sold, and Revenue.

Your goal is to create a simple summary report showing the total revenue for each product category within each region. Manually pivoting this in another tool would be a weekly chore, but in Power BI, you can build a table that updates automatically.

Step 1: Open the New Table Editor

In Power BI Desktop, make sure you're in the report view or data view. Go to the Modeling tab in the ribbon at the top of the screen. Here, you'll see an option called New table. Click it.

Step 2: Write Your DAX Formula

After clicking "New table," a formula bar will appear, just like in Excel. This is where you'll write your DAX code. Let's create a table that groups by Region and Product Category and calculates the total revenue.

Enter the following formula:

Sales Summary by Region = SUMMARIZE( 'Sales', 'Sales'[Region], 'Sales'[Product Category], "Total Revenue", SUM('Sales'[Revenue]) )

Let's review this. We are telling Power BI:

1. Create a new table called Sales Summary by Region.
2. Use the SUMMARIZE function.
3. The source table is 'Sales'.
4. Group the results by 'Sales'[Region].
5. Further group those results by 'Sales'[Product Category].
6. Create a new column named "Total Revenue" that contains the sum of 'Sales'[Revenue] for each group.

Once you've typed it out, press Enter.

Step 3: Check Your New Table

On the right-hand side in the Data pane, you will see your newly created table, Sales Summary by Region. Click on it, then switch to the Data View (the grid icon on the left) to see your result.

You'll see a clean, compact table with exactly three columns: Region, Product Category, and Total Revenue. All the row-level detail is gone, leaving you with just the aggregated numbers you need. Now you can use this table to create charts and other visuals that will be incredibly fast.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Going Further: The More Powerful SUMMARIZECOLUMNS

While SUMMARIZE works perfectly well, an even better, more modern function often recommended by Power BI experts is SUMMARIZECOLUMNS. It's generally more efficient and can handle relationships and filters in a more predictable way.

Its syntax is slightly different but arguably more intuitive:

SUMMARIZECOLUMNS(<,groupBy_columnName1>, [<,filterTable> | <,filterExpression>], ..., "<,newColumnName>", <,expression>)

Notice that you list the group-by columns first without specifying the source table initially. Let's rewrite our previous example using this function. You can also add more aggregated columns, like Total Units Sold.

Create another new table and use this formula:

Sales Summary SC = SUMMARIZECOLUMNS( 'Sales'[Region], 'Sales'[Product Category], "Total Revenue", SUM('Sales'[Revenue]), "Total Units", SUM('Sales'[Units Sold]) )

Here, we didn't have to tell the function to start with the Sales table. Power BI's engine is smart enough to figure that out from the columns we provided ('Sales'[Region], 'Sales'[Product Category], etc.). This subtle difference allows its query engine to generate a more efficient plan to fetch the data, resulting in better performance, especially on very large datasets.

General tip: If you are creating a new calculated table from scratch, it's a good practice to use SUMMARIZECOLUMNS as your default choice over SUMMARIZE.

Practical Use Cases and Examples

Now that you know the "how," let's talk more about the "why" with some practical scenarios where these tables are lifesavers.

Use Case 1: Preparing Data for a Scatter Plot

Imagine you want to create a scatter plot to see if there's a correlation between the number of customers and total profit for each city. Your main data table has transactions, not one row per city. A summarize table is perfect for this.

City Performance = SUMMARIZECOLUMNS( 'Sales'[City], "Total Profit", SUM('Sales'[Profit]), "Unique Customers", DISTINCTCOUNT('Sales'[CustomerID]) )

This gives you a tidy table with one row per city, ready to be plotted instantly on your scatter chart.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Use Case 2: Creating a Top N Report

What if you want a table that only shows your top 10 best-selling products? You could filter a visual, but creating a dedicated table makes the logic reusable. You can combine SUMMARIZECOLUMNS with other functions like TOPN.

Top 10 Products = TOPN( 10, SUMMARIZECOLUMNS( 'Sales'[Product Name], "Total Revenue", SUM('Sales'[Revenue]) ), [Total Revenue], DESC )

This formula first creates a temporary summary table of all products and their revenue, then TOPN steps in to filter it down to only the top 10 rows, sorted in descending order of revenue.

Use Case 3: Monthly Snapshots for Trend Analysis

If your main table contains daily sales data but your reports mainly focus on monthly trends, you can speed things up by pre-aggregating the data at a monthly level.

Monthly Sales Summary = SUMMARIZECOLUMNS( 'Calendar'[Year], 'Calendar'[Month], "Monthly Revenue", SUM('Sales'[Revenue]), "Monthly Customers", DISTINCTCOUNT('Sales'[CustomerID]) )

Connecting a visual to this smaller monthly table will be much quicker than calculating the monthly totals from the daily table over and over.

Quick Tips and Common Mistakes

• Be Specific with Column Names: Always specify the table name along with the column name (e.g., 'Sales'[Region] instead of just [Region]). This avoids ambiguity and potential errors in complex data models.
• Watch For Context: Remember that a calculated table is only refreshed when the data itself is refreshed. It doesn't react dynamically to filters you place in the report view, unlike a DAX measure.
• Context Transition Matters: The SUMMARIZE function does not automatically apply context transition from a row context to a filter context. SUMMARIZECOLUMNS does. This is a more advanced DAX topic, but it is another chief reason SUMMARIZECOLUMNS is generally safer for complex scenarios.

Final Thoughts

Creating summary tables with SUMMARIZE and SUMMARIZECOLUMNS is a fundamental skill that shifts your Power BI usage from basic reporting to efficient data modeling. By pre-aggregating your data into smaller, more focused tables, you dramatically speed up your reports, simplify your DAX measures, and create a data model that is far easier for you and your team to understand and manage.

While mastering DAX functions for Power BI is a powerful skill, we know it comes with a steep learning curve. Not everyone has hours to spend learning a new query language just to get answers about business performance. We built Graphed to remove that friction. By connecting your data sources and asking questions in plain English ‑ like "create a report showing total revenue by product category and region" ‑ you can instantly generate the reports and dashboards you need without writing a single line of code.