How to Show Value Instead of Aggregate in Power BI

Ever drag a field into a Power BI visual, expecting a neat list, only to be met with a single, massive number? You're not alone. Power BI's default behavior is to summarize data - summing prices, counting customers, or averaging ratings - which is incredibly useful for high-level analysis. But sometimes, you just want to see the original, un-aggregated values, like a SKU number, a year, or an order ID. In this tutorial, we’ll walk through exactly how to tell Power BI to stop summarizing and simply show your raw data.

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 Power BI Aggregates Data by Default

Before we turn it off, it helps to understand why Power BI does this in the first place. At its core, business intelligence is about condensing huge datasets into understandable insights. When you have a million sales records, an analyst almost never wants to see a list of a million individual sale prices. Instead, they want to see the Total Sales, the Average Order Value, or the Total Customers.

Aggregation (or summarization) is the 'condensing' part of that process. The most common types of aggregation you’ll see are:

• Sum: Adds all the numbers together. Perfect for calculating total revenue.
• Average: Calculates the mean of the numbers. Great for average session duration or order size.
• Count: Counts the number of rows. Useful for finding the total number of orders.
• Min/Max: Finds the smallest or largest value. Helpful for identifying the lowest price ticket or the day with the highest traffic.
• Count (Distinct): Counts the number of unique values. Perfect for calculating the number of unique customers who made a purchase.

This automatic behavior makes creating high-level dashboards fast and efficient. The problem arises when Power BI sees a column of numbers and assumes it should be aggregated, even when it shouldn’t. This is most common with identifier fields that happen to be numeric, like:

• Product SKUs
• Order IDs
• Postal Codes
• Year (who wants the sum of 2023, 2024, etc.?)

In these cases, aggregation gives you meaningless figures. You don't want the "sum" of your order IDs, you want a list of them. Let’s look at how to 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: Change the Summarization in a Specific Visual

The quickest way to fix this is directly within the visual you're building. This approach is perfect for a one-off chart or table where you need to display details instead of a summary.

Imagine you're building a table to show individual orders. You drag in ‘Category Name’, ‘Product Name’, and ‘Order ID’. Power BI immediately sums up the ‘Order ID’ field, giving you a huge, useless number for each product. Here's how to correct it in three easy steps.

Step-by-Step Guide:

1. Select Your Visual: Click on the table, matrix, or chart that is incorrectly summarizing your data.
2. Find the Field: In the Visualizations pane on the right, look at the “Values” (or columns/rows) well. You will see the field that is being aggregated (in our example, a 'Sum of Order ID').
3. Change the Summarization Setting: Click the small dropdown arrow to the right of the field name. A menu will appear with all the aggregation options (Sum, Average, Min, Max, etc.). At the bottom of this list, you’ll find the option you need: Don't summarize.

Once you click "Don't summarize," the magic happens. Power BI will stop rolling the values up into a single number and will instead display each individual Order ID as its own row in the table. Voila! You now have the detailed list you wanted.

What about Text Fields?

This same principle applies to text or categorical data. Sometimes you might drag a field like 'Customer Name' into a values well and Power BI defaults to showing 'First Customer Name' or 'Count of Customer Name'. You can use the exact same steps - clicking the dropdown and selecting 'Don't summarize' - to display all the individual names instead.

Method 2: Set the Default Summarization for a Field

The first method is great, but it can get repetitive. If you have a field like ‘Product SKU’ that you will never want to aggregate, you can change its default behavior across your entire report. This way, any time you drag that field into a new visual, Power BI will know not to sum it up.

This is a more permanent and efficient solution for identifier fields that are formatted as numbers. By setting this upfront, you save yourself future clicks and headaches.

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 Guide:

1. Go to the Data or Model View: On the left-hand side of the Power BI Desktop window, switch from the Report view (the canvas icon) to either the Data view (the spreadsheet icon) or the Model view (the diagram icon).
2. Select the Column: Find and click on the specific column you want to change (e.g., the 'Order ID' column) in the table.
3. Use the Column Tools: With the column selected, a new ribbon tab will appear at the top called Column tools. Click on it.
4. Change the Default Summarization: Look for the "Summarization" group in the ribbon. You’ll see a dropdown menu that likely says "Sum" by default. Click on this dropdown and select Don't summarize from the list.

That's it! Now, any time you use this ‘Order ID’ field in your report, it will default to showing individual values. This is hands-down the best practice for any numeric column that functions as an identifier, like IDs, SKUs, zip codes, or years.

Method 3: A Quick Fix in the Power Query Editor

Sometimes, the "problem" isn't Power BI's aggregation, but the data type itself. A column like 'Order ID' isn't really a number in the mathematical sense, you’d never perform calculations with it. It’s an identifier, which behaves more like text.

You can tell Power BI this directly by changing the column's data type in the Power Query Editor. This preempts the aggregation issue entirely.

Step-by-Step Guide:

1. From the Home tab in Power BI Desktop, click on 'Transform data' to open the Power Query Editor.
2. Find the table and column in question (e.g., 'Order ID').
3. Click the icon in the column header (it might show '123' for a whole number or '1.2' for a decimal).
4. From the dropdown menu, change the data type to 'Text'.
5. Click 'Close & Apply' in the top-left corner.

Since Power BI can't perform mathematical summarization on a text field, it will never try to sum it again. Dragging the field into a visual will now show you the raw values by default.

A quick note of caution: Only use this method if you are absolutely certain you will never need to treat the values as numbers (e.g., counting them or using them in a numerical filter). Changing the default summarization (Method 2) is often a safer and more flexible choice.

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

When to Use DAX for More Control (The SELECTEDVALUE Function)

For more advanced scenarios, a Data Analysis Expressions (DAX) formula can give you complete control over your output. This is especially useful in cards or visuals where you want to show a single value conditioned by certain filters but want to avoid showing an error or a meaningless aggregation when multiple items are selected.

Let's say you want a card visual to display the "Salesperson's Name." If you filter your report to a single region with one salesperson, you'd like it to show "name." But what if the report is filtered to multiple regions with different salespeople? You wouldn't want it to just show “Salesperson's name,” which Power BI might do by default.

The SELECTEDVALUE function is perfect for this. It checks if there is only one unique value in the filter context, and if so, displays that value. If not, you can specify a default result.

Creating a Measure with SELECTEDVALUE

1. Right-click on any table in your 'Fields' pane and select 'New measure'.
2. In the formula bar, enter something like this:

Selected Salesperson = SELECTEDVALUE('Sales'[Name], "Multiple Selected")

1. Use this new measure in your card visual instead of the 'Salesperson Name' field.

Now, your card will dynamically update:

• When a single salesperson is filtered, it shows their name (e.g., "John Smith").
• When multiple salespeople are in the filter context, it will display "Multiple selected."

This DAX method gives you precise control over the user experience and prevents Power BI from making guesses about your data's most aggregated state.

Final Thoughts

Mastering how and when not to summarize in Power BI is a fundamental skill that turns frustration into control. Whether you choose to make a quick visual adjustment, set the default behavior of the column, or use advanced DAX formulas, you will have the power to show your data the way you want it.

The intricacies of configuration and UI decisions are why we built Graphed. We want to make the process of getting data into meaningful visuals seamless. Instead of clicking through multiple menus, with Graphed, you can simply select an English prompt, such as "Show me total orders by day for the last month," and get an instant dashboard built for you. Deal with what to summarize and how so you can stay focused on what your data means, rather than how to get it to show what you want.