How to Remove Summarization in Power BI

Power BI is designed to analyze large datasets by adding up, or summarizing, numeric columns automatically. While this is great for creating headline stats like total revenue or units sold, it can be frustrating when you just want to see your data as-is. If you've ever dropped a column of Product IDs or Zip Codes into a visual and watched in confusion as Power BI tried to sum them up, you're in the right place. This article will walk you through exactly how to remove and control data summarization in Power BI.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Summarization and Why Does Power BI Use It?

By default, when you drag a column containing numeric data into a visual or the values area of a report, Power BI performs an action called summarization or aggregation. It assumes you want to see a single, summarized value representing all the rows in that column.

The most common summarization it uses is Sum. For example, if you have a Sales table with 10,000 rows and a 'Revenue' column, Power BI will calculate the sum of all 10,000 revenue entries to show you one number: Total Revenue. Other default aggregations include:

• Average
• Minimum
• Maximum
• Count
• Count (Distinct)

This behavior is intentional and powerful. It’s what allows you to quickly create executive dashboards and get a high-level view of your business performance without having to write code. The problem arises when you're working with numbers that aren't meant to be added together - things like identifiers, years, or ratings.

Luckily, you have several ways to take back control and tell Power BI how you want your data treated.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: Use "Don't Summarize" From the Column Tools

The most direct way to permanently stop a column from being summarized across your entire report is by setting its default property to "Don't Summarize." This changes the behavior at the data model level, so you only have to do it once per column.

This is the preferred method for any fields that should never be aggregated, such as IDs, product codes, or year numbers.

Step-by-Step Instructions

1. Click on the numerical column you want to change in the Data pane on the right-hand side of your report. Let's use a "Product ID" column as an example.
2. Once selected, a new menu tab called Column tools will appear in the top ribbon.
3. Within the "Column tools" tab, look for the Properties section. Here, you'll see a dropdown menu labeled Summarization. By default, it's likely set to "Sum."
4. Click the dropdown and select Don't summarize from the list.

That's it. Now, whenever you drag the "Product ID" field into a table, matrix, or any other visual, Power BI will display it as a list of distinct values rather than trying to calculate the sum of all IDs. It will treat the column as a category or dimension, which is exactly what you want.

When to use this method:

• For all identifiers: Product IDs, Order Nos, Customer IDs, Zip Codes, Employee IDs, etc.
• For helper columns: Columns with nominal numbers like sort order, versions, or status codes.
• For year fields: If your 'Year' column is a whole number like 2023, 2024, etc., Power BI will try to sum it. Changing it to "Don't Summarize" ensures it acts as a filter or category instead.

Method 2: Change the Aggregation for a Single Visual

Sometimes you need to show the detailed, row-level data in just one specific chart or table, without changing the default behavior for the column everywhere else in your report. For example, your 'Sales' column should usually be summed, but you might want one table that lists every individual sale.

In this case, you can override the summarization for that visual only.

Step-by-Step Instructions

1. Create or select a visual in your report, such as a Table.
2. Drag the numeric field you want to show into one of the visualization wells (for a table, drag it into the Columns field).
3. In the visualization well where you just dropped the field, you'll see its name with a "Sum of" prefix (e.g., "Sum of Sales").
4. Click the small down arrow next to the field name. A context menu will appear.
5. From the options in this menu, select Don't summarize.

The visual will immediately update to show the individual values from that column. This change is entirely local to this single visual. If you drag the same 'Sales' field into a different visual, it will once again default to a SUM, which is often the desired behavior.

When to use this method:

• Creating detail tables that list raw transactional data.
• Investigating outliers by showing individual data points behind an aggregated chart.
• When you need a field to behave differently in two separate visuals (e.g., total sales in a card-visual vs. individual sales in a table).

Method 3: Adjust Settings in the Original Data Source

If you prefer a proactive clean-up approach, you can actually set the summarization preference outside of your direct Power BI instance, meaning your team, regardless of where they are opening the visuals, can view data the way it was designed to show.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Adjusting Data via Power Query

One way you can edit the categorization is prior to visual development using Power Query Editor which allows you to adjust not just categorization but perform several data-altering techniques including, but not limited to, grouping data, merging or changing your value types.

Change Data Type

Power Query is able to assess data by type, so for a product ID, a simple change you might make is converting all values to "text" rather than numerical, an easy edit that has long-term advantages. Any IDs converted to strings will no longer be subjected to calculations as numerical values - making your IDs just identifiers as they should be.

When to use this method:

• To save you more time during data development - especially if you are working on a very large data set
• Helps to ensure you're working with clean data & minimizes confusion or miscalculation by Power BI
• Making bulk edits through DAX code or direct changes in Power Query Editor is much faster than manual adjustments.

Best Practices for Managing Data Summarization

Controlling summarization is a fundamental part of building effective and accurate Power BI reports. Here are a few tips to make it a seamless part of your workflow.

1. Set Defaults Immediately After Importing Data

Get into the habit of reviewing your data model right after you load tables into Power BI. Go through your columns in the Data view, especially those with numbers. For anything that is an ID, a code, or a year, use Method 1 to set its default summarization to "Don't Summarize." This five-minute practice can save you hours of frustration and report corrections later on.

2. Understand Implicit vs. Explicit Measures

An implicit measure is what Power BI creates automatically when you drag a numeric field into a visual. It's quick, but it's not very flexible and can lead to confusion because the calculation is hidden. An explicit measure is what you create by using DAX code to define a specific calculation that suits your needs. This gives you much more control over how your data is aggregated and displayed.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Be Intentional With Data Categories

In addition to setting summarization properties, accurately defining your data categories in the Column tools section is a powerful model-driven approach that helps Power BI use visuals more effectively.

• By going through some of the default changes, you can make a difference - for example, Postal vs. ZIP Code & state and country, etc. Correcting some minor formatting issues can help Power BI automatically identify these with geocodes in visuals.

Making a custom change in data categories and summarizing by extension does more to give some control as users use reports or filter what values appear on their screens. The primary value from updating summaries is that it provides direction in the interpretation of charts leading to an optimal display. For more great resources, including full guides, please visit us here.

Final Thoughts

Gaining control over Power BI's automatic summarization is a critical skill for any report builder. By understanding why it happens and how to manage it - either permanently for a column with "Don't Summarize," temporarily for a single visual, or by creating robust DAX measures - you can ensure your visuals display your data exactly as you intend. This leads to clearer, more accurate reports and fewer head-scratching moments.

We believe that you shouldn't have to become a data modeling expert just to get a simple chart of your business performance. At Graphed, we handle all this complexity for you. Simply connect your data sources, and our AI will understand the difference between a Product ID and sales revenue automatically, creating real-time dashboards and reports that are always accurate. Instead of navigating menus, just ask for what you need, and let us take care of the rest.