How to Change Count to Sum in Power BI

It's a frustratingly common Power BI moment: you drag a column of numbers, like "Sales" or "Revenue," into a visual, and instead of a grand total, you get a simple count of the rows. You wanted the sum of your sales, but Power BI gave you the number of transactions instead. This article will walk you through exactly why this happens and provide three clear methods to fix it, from a quick adjustment to a permanent solution.

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 Count Instead of Sum?

This problem almost always comes down to one thing: data type. Power BI is incredibly smart, but it can only act on what it knows. When you import data, Power BI analyzes each column and assigns it a data type, such as Text, Whole Number, Decimal Number, or Date.

If your numeric column is accidentally classified as a "Text" data type, Power BI won't offer to sum it. After all, you can't do math on letters or words. So, it defaults to the only logical operation it can perform on any type of data: counting the entries. This mix-up often happens for a few subtle reasons:

• Hidden Characters: Pesky currency symbols ($), commas (,), or spaces within your numbers can cause Power BI to misinterpret the entire column as text.
• Mixed Data: If most of a column's values are numbers but a few rows contain text like "N/A," "Pending," or are completely blank, the whole column might be categorized as text to accommodate those non-numeric values.
• Import Glitches: Sometimes, the data source itself doesn't properly define the column type, leaving Power BI to make its best guess - which is occasionally wrong.

Understanding this helps us tackle the problem correctly. We can either override the default behavior for a single visual or fix the underlying data type issue for good.

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 in the Report View

If you're in a hurry and just need to change the calculation for a single chart, card, or table, this is the fastest way. You are essentially telling Power BI to override its default summarization for this specific visual.

Let's say you've created a card to show total revenue, but it's showing "Count of Revenue."

Step-by-Step Instructions:

1. Select the Visual: Click on the card, bar chart, or table you want to change. This will activate the Visualizations pane on the right-hand side.
2. Find the Field: In the Visualizations pane, look for the data field you're using. It will typically be under a header like "Values," "Y-axis," or "Columns." You'll see your field name, likely with "Count of" preceding it.
3. Change the Summarization: Click the small downward-facing arrow next to the field name. A context menu will appear with a list of summarization options (Sum, Average, Minimum, Maximum, Count, etc.).
4. Select "Sum": Simply choose Sum from the list. Your visual will immediately update to show the total sum of the values instead of the count.

This method is perfect for quick, one-off changes. However, if you plan to use this "Revenue" field in multiple visuals, you'll have to repeat this step every single time, which can become tedious. For a more permanent solution, you'll want to change the default behavior for the field itself.

Method 2: The Permanent Fix in the Data Model

Wouldn't it be great if Power BI just knew to sum your "Sales" column every time you used it? You can set this up by changing the column's default summarization in the data model. This tells the entire report how to treat that column from now on.

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. Go to Table View or Model View: On the left-hand side of the Power BI window, click either the "Table" icon (it looks like a grid) or the "Model" icon (it looks like a diagram with relationships).
2. Select Your Column: In the "Table" view, find and click the header of the column you want to fix (e.g., "Sales"). In the "Model" view, find the table and click the field name.
3. Use the Column Tools: Once the column is selected, a new contextual tab will appear in the top ribbon called Column tools. Click on it.
4. Set the Default Summarization: In the "Column tools" ribbon, look for the "Properties" group. You'll find a dropdown menu for Summarization. By default, it might be on "Don't summarize" or "Count."
5. Change it to "Sum": Click the dropdown and select Sum.

That’s it! Now, any time you drag that field into a new visual, it will automatically default to using the Sum aggregation. This method saves a lot of time and ensures consistency across your report.

Method 3: Fixing the Root Cause in Power Query

What if you try the methods above, but "Sum" is greyed out or not even an option? This is a clear sign that you're dealing with the root problem we discussed: an incorrect data type. To fix this, we need to go one step deeper into the Power Query Editor, the engine room where you can clean and transform your data before it loads into the report.

This is the most robust solution because it ensures the data is clean and correctly formatted right from the source.

Step-by-Step Data Cleaning:

1. Open the Power Query Editor: In the main Power BI window, go to the Home ribbon and click on Transform data. This will open the Power Query Editor.
2. Find the Problem Column: On the left, select the query (table) that contains the column you need to fix. Then find the column within the data preview grid.
3. Check the Data Type: Look at the icon next to the column header.
4. Change the Data Type: Click the ABC icon. A dropdown menu will appear with a list of data types. Select either Decimal Number (for numbers with decimals, like price or revenue) or Whole Number (for integers, like quantity sold).
5. Troubleshoot Errors: When you change the data type, one of two things will happen:
6. Clean the Data (if you have errors): If you see errors, you need to clean the junk data before converting the type.
7. Re-apply the Data Type: After cleaning the column, repeat step 4 to change the data type to a numeric one. This time, it should work without creating a new error step.
8. Close & Apply: Once your column has the correct numeric data type (the 1.2 or 123 icon), click the Close & Apply button in the top-left corner of the Power Query Editor.

Now, your data model is updated with a properly formatted numeric column. You may still need to use Method 2 to set the default summarization to "Sum," but you've addressed the foundational issue that prevented Power BI from recognizing your data as numbers in the first place.

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

Final Thoughts

Facing the "Count instead of Sum" issue is a rite of passage for many Power BI users. It’s almost always caused by an incorrect data type designation, but as we’ve seen, you have multiple ways to solve it. You can make a quick fix directly on a visual, set a permanent default for the entire report in the data model, or dive into Power Query to fix the data at its source for the cleanest, most reliable results.

While mastering these small steps in tools like Power BI is part of the learning curve, they can accumulate and slow down your primary goal: getting answers from your data. At Graphed, we remove this friction by letting you use simple language instead of clicking through menus to fix summarizations or data types. You can connect your data sources, like Google Analytics and Salesforce, and just ask, "Show me a chart of my total revenue by month," and our AI handles the analysis to instantly generate the correct dashboard, sum and all. This frees you to focus on the insights, not the setup.