How to Use Summarize in Power BI

Power BI is an incredible tool for visualizing data, but its real power comes from DAX (Data Analysis Expressions). The SUMMARIZE function is one of the most versatile tools in the DAX library, allowing you to create custom summary tables on the fly. This article will walk you through exactly how SUMMARIZE works, from basic grouping to more advanced applications, giving you the skills to unlock deeper insights from your data.

What Exactly is the SUMMARIZE Function?

In short, the SUMMARIZE function creates a new virtual table by grouping data from an existing table. Think of it as Power BI’s version of a SQL GROUP BY statement or an Excel Pivot Table but in the form of a DAX function. You tell it which table to look at, which columns you want to group by (like Product Category, Country, or Date), and optionally, what calculations you want to perform on those groups (like Total Sales, Average Clicks, or Transaction Count).

The result is a new, aggregated table that doesn't physically exist in your model but can be used in other calculations or visualized directly. This is extremely useful for calculating complex metrics or preparing data for a specific type of chart where the raw data just won't work.

When Should You Use SUMMARIZE?

While you can accomplish simple grouping and summarization through the standard Power BI visuals, there are several scenarios where SUMMARIZE becomes essential:

• Creating Custom Summary Tables: Sometimes you need a specific table view that doesn't exist in your model. For instance, you might want a table that only shows total sales per customer per month. SUMMARIZE can create this table for you to use in a table or matrix visual.

• Debugging DAX Formulas: DAX can get complicated fast. SUMMARIZE is a great debugging tool because it lets you see the intermediate virtual table that a measure is calculating. This can help you understand if your aggregations are working as expected before you wrap them in more complex logic.

• Calculating Sophisticated Aggregations: Its most powerful use is as a building block inside other functions. For instance, if you want to find the average of a sum (like average daily sales for a month), you first need to create a summary table of sales per day. SUMMARIZE creates that temporary table, which another function like AVERAGEX can then iterate over.

The Syntax of SUMMARIZE

Understanding the structure of the function is the first step. At first glance, it might look a bit intimidating, but it's quite logical once you break it down.

Let's go through each part:

• <Table>: The physical table in your data model that you want to summarize. For example, 'Sales' or 'MarketingLeads'.

• <GroupBy_ColumnName>: The column(s) you want to group your data by. You must specify at least one. If you want to group by sales region and then product category, you'd list both columns here.

• <Name>: This is the name you give your new calculated column, wrapped in double quotes (e.g., "Total Sales Amount").

• <Expression>: This is the DAX calculation that populates the new column. This is typically an aggregation like SUM('Sales'[Revenue]) or COUNTROWS('Leads'). You can have multiple sets of Name/Expression pairs, one for each calculation you want to add.

Putting it to Work: Step-by-Step Examples

The best way to learn is by doing. Let's start with a very simple example and build up. Imagine we have a basic 'Sales' table with columns like Product Name, Product Category, Country, Order Date, and Revenue.

1. Simple Grouping

First, let's create a table that simply lists all the unique product categories. We won't add any calculations yet.

In the "Data" view of Power BI, navigate to the "Table tools" ribbon and click on "New Table." Then, enter this DAX formula:

Power BI will instantly generate a new, single-column table named "Product Categories" that contains a unique list of all categories from your original sales data. It's that simple.

If you wanted to see the unique combinations of Category and Country, you would just add the second column:

This creates a two-column table listing every unique combination of product category and country where a sale has occurred.

2. Grouping with Calculations

(Aggregated Columns)

This is where SUMMARIZE really starts to shine. Listing unique values is helpful, but we usually want to calculate something about those groups. Let’s calculate the total revenue for each product category.

Create another new table with the following formula:

Let's break this one down:

• 'Sales': We are summarizing our main sales table.

• 'Sales'[Product Category]: We are grouping the data by this column.

• "Total Billed Revenue": This is the name we chose for our new calculation column.

• SUM('Sales'[Revenue]): This expression calculates the sum of the Revenue column for each group.

The resulting table will have two columns: Product Category and Total Billed Revenue, giving you a clean summary of your performance.

3. Using SUMMARIZE Inside a Measure

Creating physical summary tables is cool, but the real magic happens when you use SUMMARIZE to create a virtual table inside a measure. Let's tackle that common but tricky "average of a sum" problem: calculating average daily sales.

You can't just take AVERAGE('Sales'[Revenue]) because that would give you the average of every single transaction, not the average of the daily totals. To do this correctly, you must first calculate the total sales for each day and then find the average of those daily totals.

Click "New Measure" and enter this formula:

Let’s unpack this powerful one-liner:

• The SUMMARIZE part is the core. It creates a temporary virtual table with two columns: Order Date and Daily Sales (which is the total revenue for each date).

• The AVERAGEX function then iterates over that temporary table, row by row.

• For each row, it looks at the value in the [Daily Sales] column and calculates the average of all those values.

This pattern—using SUMMARIZE to shape the data correctly and then feeding it into an iterator function like AVERAGEX, SUMX, or MAXX—is one of the most important concepts for mastering advanced DAX.

Best Practices and Common Pitfalls

While SUMMARIZE is an incredible tool, there are a few things to keep in mind to get the best results and avoid confusion.

SUMMARIZE vs. SUMMARIZECOLUMNS

If you've been working with DAX for a while, you may have also seen SUMMARIZECOLUMNS. This is a newer, more optimized function that is generally recommended for most summary queries. So what’s the difference?

While their syntax looks similar, they behave differently behind the scenes, especially with how they handle "filter context" — which is Power BI slang for the active filters from slicers, charts, or other DAX formulas. SUMMARIZECOLUMNS is designed to work more predictably within the existing filters on your report. For new calculations, especially in measures, it's often better to start with SUMMARIZECOLUMNS.

However, SUMMARIZE is still very useful, especially for creating isolated, physical tables or when you need more explicit control over the filter context. For now, just know that SUMMARIZECOLUMNS is the modern go-to, but learning SUMMARIZE is still foundational.

Performance Considerations

Creating summary tables on the fly is powerful, but it does require computing resources. If you are summarizing a massive table with millions of rows, using a SUMMARIZE function within a measure can sometimes make your reports feel slow, as Power BI has to perform that calculation every time a user interacts with a visual.

If your summary table doesn't need to change dynamically with user filters, it's often more performant to create it as a physical, calculated table during the data refresh. This does the heavy lifting once, so your report visuals remain fast and responsive.

Final Thoughts

The SUMMARIZE function is a fundamental building block in DAX that shifts you from simply visualizing data to actively shaping and analyzing it. By allowing you to group, aggregate, and create tailored virtual tables, it opens the door to insights that are impossible to derive with basic visuals alone and is the key to calculating complex, meaningful KPIs.

As helpful as this is, spending time writing DAX can still feel like a productivity tax. At Graphed, we've automated this entire analytics workflow. Instead of building summary tables by hand, you can just ask questions in plain English like, "show me total sales by product category and country as a table" or "make a line chart showing our average daily sales this quarter." We automatically connect to your data, generate the right visualizations, and turn hours of manual report building into 30 seconds of conversation. If you're ready to get insights without getting stuck in the weeds, give Graphed a try.