How to Remove Aggregation in Power BI

Dragging a column of numbers into a Power BI visual usually gives you a single, summarized value - like a total sum. But what if you want to see all the individual rows of data instead? This process is all about telling Power BI to show the details instead of the summary. This tutorial will walk you through exactly how and when to remove aggregation in your Power BI reports so you can see your data at the most granular level.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is Data Aggregation Anyway?

Data aggregation, in simple terms, is the process of summarizing data from many rows into a single value or a smaller set of values. When you add a numeric field like "Sales" to a Power BI visual, it automatically performs an aggregation - most commonly, a SUM. Instead of showing thousands of individual sale amounts, it shows you the total.

Other common aggregations include:

• Average: The average value of all numbers in the column.
• Count: The total number of rows.
• Count (Distinct): The number of unique values in a column.
• Min: The smallest value.
• Max: The largest value.

Power BI does this by default for two main reasons: performance and clarity. It's much faster to calculate and display a single total than it is to render tens of thousands of individual data points. Plus, high-level summaries are often what dashboards are for - giving you a quick overview of your business performance.

But sometimes, the big picture isn't enough. Sometimes you need to see the individual brushstrokes, and that’s when you need to turn aggregation off.

Why Would You Want to Remove Aggregation?

While default summarization is useful for dashboards, there are many common scenarios where you'll want to see the underlying, non-aggregated data. This is often referred to as viewing data at a "row level" or at the finest "granularity."

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

1. Creating Detailed Tables and Lists

The most common reason to remove aggregation is to create a detailed table, similar to what you’d see in an Excel or Google spreadsheet. Imagine you want to view a list of every single customer order from the last month, including the Order ID, Customer Name, Sale Date, and Sale Amount for each. Aggregation would just give you the total sales, but removing it lets you see each transaction on its own line.

2. Data Verification and Troubleshooting

When you first load a dataset, you often need to check the raw data to make sure everything was imported correctly. Summaries can hide issues. For instance, a total sales figure might look correct, but a row-level view could reveal duplicate entries, missing customer names, or incorrect formatting that you would have otherwise missed.

3. Using Certain Visualizations like Scatter Charts

Scatter charts are designed to plot individual data points to reveal relationships, trends, or clusters. For example, you might want to plot a point for each product you sell, with its manufacturing cost on the X-axis and its profit margin on the Y-axis. If Power BI aggregates the data, it might only show you one point representing the SUM of all costs vs. the SUM of all profits, which is useless. To see each product as a unique dot, you need to de-aggregate the data points.

4. Performing Specific Row-Level Calculations

For more advanced analysis with DAX (Data Analysis Expressions), you might need to build measures that work on a row-by-row basis. Functions like SUMX or AVERAGEX iterate through a table one row at a time to perform a calculation. While these DAX functions don't always require you to physically turn off aggregation in a visual, understanding the concept is fundamental to writing them correctly.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

How to Turn Off Aggregation in Power BI: 3 Simple Methods

Fortunately, Power BI makes it very easy to control aggregation on a field-by-field basis. Here are the most practical methods.

Method 1: Change Summarization in the Visual ("Don't Summarize")

This is the quickest and most common method you'll use. It changes the aggregation for a field within a single, specific visual, without affecting any other visuals in your report.

Let's say you're building a table and want to see every individual sale, not the total.

1. Click on the visual you want to change (for instance, a Table visual).
2. In the Visualizations pane, look at the fields you've added under the "Values" or "Columns" well. You'll see your numeric field listed with its aggregation type, such as "Sum of Sales Amount."
3. Click the small downward-facing arrow next to the field name.
4. A menu will appear. Simply select "Don't summarize" from the list.

That's it! Your visual will instantly update to show each individual row of data for that column instead of the summarized total.

Method 2: Change the Default Summarization for a Field

If you have a field that you never want to be aggregated by default (like an Order ID, Year, or employee ID number), you can change its default behavior across your entire report. This saves you from having to change it to "Don't summarize" every time you use it.

1. Go to either the Data View (the grid icon on the left) or the Model View (the diagram icon on the left).
2. Find and select the column you want to modify from the Data pane on the right.
3. With the column selected, a new ribbon tab called Column tools will appear at the top.
4. In the "Properties" section of this ribbon, find the Summarization dropdown menu.
5. Click the dropdown and change it from "Sum" (or whatever it is) to "Don't summarize."

Now, whenever you drag this field into any new visual, its default behavior will be to show individual values, not an aggregation.

Pro Tip: This is a best practice for ID fields or numeric codes that shouldn't be added together mathematically. Think Product IDs, Postal Codes, or Customer IDs.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 3: When Dealing with Text or Identifier Fields

Even though summarization mostly applies to numbers, Power BI might try to summarize text fields as well by showing the "First," "Last," or "Count (Distinct)" of the text values.

If you add a "Product Name" column to a card visual, it might just show you the first product name in your table alphabetically. If you want a list of all product names in a table visual, you follow the same steps as Method 1: drag the text field in, click the dropdown arrow next to its name, and select "Don't summarize."

A Practical Example: Building a Detailed Sale Log

Let's put this into practice. Imagine your goal is to create a simple table showing every order from your sales data.

1. Start by clicking the Table icon in the Visualizations pane to add a new table to your report canvas.
2. From your Data pane, drag the following fields into the "Columns" well of the table visual:
3. At this point, you'll probably see something odd. The "Order ID" might show a sum (which is meaningless), and "Sale Amount" will definitely show a sum. Your table displays a summary, not a detailed log.
4. To fix it, go to the "Columns" well, click the dropdown arrow next to Sum of Sale Amount, and select "Don't summarize."
5. Do the same for Sum of Order ID. (Better yet, use Method 2 to set the default summarization for "Order ID" to none permanently).
6. Your table will now transform into a detailed log, listing each order on a separate line with its corresponding data - just like you wanted.

Potential Pitfalls & Best Practices

• Performance Impact: The biggest consideration is performance. Asking Power BI to display millions of individual rows in a table will be drastically slower than displaying a simple bar chart with 10 summarized categories. If your report feels slow, excessive de-aggregation in large tables is a likely culprit.
• Use the Right Visual for the Job: Removing summarization is primarily for Table and Matrix visuals. Bar charts, pie charts, and KPI cards are designed to consume aggregated data. Forcing them to display non-aggregated data can result in messy and unreadable visuals.
• Check Your Data Types: Ensure your columns have the correct data types set in the Power Query Editor. Numbers you don't want to sum (e.g., Postal Codes, IDs) could even be set as a Text type to prevent accidental aggregation from the start.

Final Thoughts

Knowing how to turn aggregation off in Power BI is a fundamental skill that gives you complete control over how your data is displayed. By using the "Don't summarize" option, you can quickly move from high-level overviews to detailed, row-level views, which is essential for data validation, building detailed lists, and deep-dive analysis.

Learning the nuances of tools like Power BI can feel like a steep climb. We built Graphed to remove that friction completely. Instead of clicking through menus and wrestling with settings, you can connect your data sources (like Google Analytics, Shopify, QuickBooks, and more), then simply describe the report you want in plain English. Graphed uses AI to build you a live, interactive dashboard in seconds, turning hours of tedious work into a simple conversation and giving you back time to focus on what the data actually means.