How to Not Summarize Values in Power BI

Power BI is designed to aggregate your data, automatically summing up any numeric columns you add to a visual. While this is great for creating high-level KPIs and summary reports, it can be frustrating when you just want to see a simple list of individual transaction IDs, product SKUs, or survey responses. Fortunately, telling Power BI not to summarize a value is straightforward. This tutorial will walk you through exactly how to control data summarization, so you can display your data at the level of detail you need.

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

Why Does Power BI Summarize Data by Default?

Before jumping into the "how," it's helpful to understand the "why." Power BI is fundamentally a business intelligence and data visualization tool. Its primary job is to help you analyze large datasets by rolling them up into meaningful charts and numbers. The most common way to do this is through aggregation - combining many individual rows into a single summary statistic.

When you import data, Power BI scans each column and assigns it a data type and a default summarization behavior:

• Numeric Columns (like Revenue, Quantity, Clicks): Power BI assumes these are quantities you want to measure. It defaults to summing them because that’s the most common analytical operation.
• Text and Date Columns (like Customer Name, Campaign, Date): Power BI assumes these are categories or dimensions you want to use for grouping or filtering. It doesn’t try to summarize them.

The problem arises with numbers that aren't meant to be added together, such as:

• Identifier Numbers: Order IDs, Customer IDs, Product SKUs, Zip Codes.
• Categorical Numbers: Employee Grade Levels, Survey Ratings (e.g., 1-5 scales).
• Year Values: A column containing the year (e.g., 2023, 2024). Summing these makes no sense.

When you drag a field like 'Order ID' into a table, Power BI sees numbers and thinks, "I should add these up!" It gives you one big number - the sum of all Order IDs - instead of the list of individual orders you wanted. Let's fix that.

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: The Quick Fix Using 'Don't Summarize'

The fastest way to stop summarization for a specific column in your report is by using the "Summarization" setting in the ribbon. This is the best choice for one-off adjustments or when you're exploring the data in a visual. It tells Power BI to treat the column as a distinct list of values, not a number to aggregate.

Step-by-Step Instructions:

1. Select a Numeric Column: In the Data pane on the right-hand side of your report view, click on the field that you want to prevent from summarizing. This could be something like OrderID or ProductSKU. When you select it, the Column tools tab will appear in the top ribbon.
2. Locate the Summarization Group: Click on the Column tools tab. Look for the Properties group on the left.
3. Change Summarization Setting: Inside the Properties group, you'll see a dropdown menu labeled Summarization. By default, it's likely set to Sum. Click the dropdown and select Don't summarize.

That's it! As soon as you set the field to "Don't summarize," Power BI will immediately stop aggregating it. If you have that 'Order ID' field in a table visual, it will instantly switch from showing a single, giant summed number to displaying each individual Order ID in its own row.

When to Use This Method:

This method is perfect for columns containing numbers that should be treated as categories. Think of identifiers like zip codes, order numbers, social security numbers, or employee IDs. They are numerically formatted, but their mathematical sum is completely meaningless.

Method 2: The Permanent Fix by Changing the Data Type

Setting "Don't summarize" is great, but it has to be done for each field individually. For columns that will never be used in a calculation, a more permanent and robust solution is to change the data type to Text. This is done inside Power Query Editor and tells Power BI at a foundational level that this column contains labels, not numbers.

Once a column's data type is Text, Power BI will never try to sum, average, or otherwise calculate it. This can prevent errors and confusion, especially for other team members who might use your dataset later.

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

Step-by-Step Instructions:

1. Open Power Query Editor: From the Home tab in the main Power BI window, click Transform data. This will launch the Power Query Editor in a new window.
2. Find your Column: On the left, select the query containing the column you want to change. Find the column in the data preview window.
3. Change the Data Type: Click the small icon in the column header (it might look like "123" for a whole number or "1.2" for a decimal). A dropdown menu will appear with a list of data types. Select Text.
4. Apply Your Changes: After changing the data type, click Close & Apply in the top-left corner of the Power Query Editor to save your changes and return to the main report view.

Now, your OrderID, ProductSKU, or ZipCode field is treated as plain text. When you drag it into a visual, Power BI will automatically show each unique value without attempting any summarization.

When to Use This Method:

Use this method for any keys or identifiers where you are absolutely certain no mathematical operations will ever be needed. This is the industry best practice for handling identifiers because it enforces data integrity from the start.

Method 3: Working with Fields in a Visual Directly

Sometimes you only want to change the aggregation behavior inside a single chart or table, without altering the default setting for the entire report. You can easily do this in the visualization's settings.

Let's say you have a Revenue field that defaults to Sum, but in one specific table, you want to see the average revenue per transaction instead.

Step-by-Step Instructions:

1. Add the Field to a Visual: Drag your numeric field (e.g., Revenue) into a visual like a Table or Matrix. It will appear as Sum of Revenue by default.
2. Open the Field's Context Menu: Go to the Visualizations pane. Find the field you just added under its field well (e.g., in the Columns, Values, or Rows bucket).
3. Click the Dropdown Arrow: Click the small downward-facing arrow next to the field name. A menu will pop up with different summarization options.
4. Select a Different Aggregation (or No Aggregation): From this menu, you can select Don't summarize to show the raw values. You can also switch the aggregation to Average, Minimum, Maximum, Count (Distinct), or Count.

This gives you ultimate flexibility on a per-visual basis, allowing the same field to be summed in one chart, averaged in another, and displayed as raw data in a table.

Bonus Tip: Controlling Date Hierarchies

Power BI performs a similar kind of automatic grouping on date fields called a "date hierarchy." When you drag a date field into a visual, it often breaks it down into Year, Quarter, Month, and Day. This is a form of summarization.

If you want to disable this and just see the original, full date (e.g., 1/15/2024), you can override it easily:

• In the Visualizations pane, find your date field in the field well (e.g., Axis or Rows).
• Click the dropdown arrow next to the field name.
• In the context menu, you will see two options: Date Hierarchy and the original name of your date field (e.g., SaleDate).
• Select the original field name (SaleDate), and the visual will change to show the distinct chronological dates instead of the grouped hierarchy.

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

A Real-World Example: Building a Transaction Log

Let's tie it all together. Imagine you have sales data with the columns: OrderID, ProductSKU, SaleDate, and Revenue.

Your Goal: Create a simple table that shows every individual transaction.

1. You drag all four fields into a new Table visual.
2. What Happens:

The table shows a single, unhelpful row summarizing your entire business.

1. The Fix:

The result is now a clean, granular log of every single order - exactly what you set out to build.

Final Thoughts

Controlling data summarization is a fundamental skill for moving beyond simple dashboards in Power BI. By fluently using the "Don't summarize" option, changing data types, and managing visual-level aggregations, you gain full control over the level of detail in your reports, allowing you to create anything from high-level summaries to detailed transactional tables.

Building effective reports is all about translating raw data into clear, actionable information as quickly as possible. We built Graphed because we believe the process of answering business questions should be even simpler. Instead of adjusting summarization settings or changing data types, you can just connect your data sources and ask questions in plain English - like "show me a list of all orders from last month sorted by revenue" - and our AI builds the report for you instantly, with no summarization guesswork. It's the fastest way to get from data to decision.