Why Is Power BI Showing Count Instead of Value?

Seeing a count of your data in Power BI instead of the actual sum is a classic hiccup that trips up beginners and experienced users alike. You drag a sales column into a chart expecting to see a total revenue figure, but instead, you get the number of sales transactions. This article will walk you through exactly why this happens and provide clear, step-by-step instructions to fix it for good.

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 My Numbers?

There's one primary culprit behind this issue: Power BI thinks your numbers are words. In the world of data, every column has a specific "data type" that tells the software how to treat its contents. When a column is correctly identified as a numerical type (like a Whole Number or Decimal Number), Power BI knows it can perform mathematical operations on it - like summing, averaging, or finding the minimum.

However, if a column is categorized as a "Text" data type, Power BI sees the contents as simple labels or strings of characters. Just as you can't algebraically add "cat" and "dog," Power BI can't add the text '100' and '50'. The only mathematical thing it can do is count how many text entries there are in the column.

This misclassification usually happens for one of a few reasons:

• Source Data Formatting: The numbers in your original file (like an Excel sheet or CSV) might contain currency symbols (e.g., "$150.00"), commas (e.g., "1,200"), or other non-numeric characters that cause Power BI to default to a text type upon import.
• Mixed Data Types: A single text entry, even an accidental one like "N/A" or a misplaced header in a single row, can force the entire column to be treated as text.
• Manual "Don't Summarize" Setting: In some cases, the data type is correct, but the column's default summarization property has been set to "Count" or "Don't Summarize."

The good news is that fixing this is usually quick and straightforward. Let's look at the methods, starting with the simplest.

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

The Easiest Fix: Changing Data Types in the Report View

This is the fastest way to correct the issue directly within the main Power BI Desktop interface without entering the data transformation layer.

Step-by-Step Guide:

1. Navigate to the "Data" view: On the left-hand side of the Power BI window, you'll see three icons: Report, Data, and Model. Click on the middle icon, which looks like a grid, to go to the Data view.
2. Select the problem column: In the Fields pane on the right, find and click on the table containing your data. Then, in the main grid view, find and click the header of the column that is being counted instead of summed. The entire column will become highlighted.
3. Use the "Column tools" ribbon: Once the column is selected, a new contextual tab called Column tools will appear in the top ribbon. Look for the "Formatting" section within this ribbon.
4. Change the Data type: You will see a "Data type" dropdown menu. It will likely say "Text." Click on this dropdown and change it to the appropriate numeric type.
5. Confirm the change: Power BI might show a warning about the potential impact of this change. As long as you are confident the column contains only convertible numeric data, you can safely confirm the change.
6. Check your visual: Switch back to the "Report" view (the top icon on the left). Your visual should now update automatically. Click on the visual, and in the "Visualizations" pane, check your data field. The aggregation should now default to "Sum" instead of "Count."

The Robust Fix: Changing Data Types in Power Query Editor

While the first method is fast, fixing the data type in the Power Query Editor is a more permanent and robust solution. Changes made here become a formal step in your data import process, meaning the data type will be correctly set every single time your report refreshes. This is the best-practice approach.

Step-by-Step Guide:

1. Open the Power Query Editor: In the "Home" tab of the main Power BI ribbon, click the Transform data button. This will launch a new window: the Power Query Editor.
2. Locate your query and column: On the left side of the editor, in the "Queries" pane, select the table you need to fix. Then, in the main data preview area, scroll to find the column causing the issue.
3. Identify the data type icon: Look at the small icon to the left of the column header's name. A text column will show an "ABC" icon. A whole number shows "123", and a decimal number shows "1.2".
4. Click the icon to change the type: Click on the "ABC" icon. A context menu will appear with a list of all available data types. Select the correct numeric type (e.g., Whole Number, Decimal Number).
5. Confirm the step: A dialog box will ask if you want to replace the current conversion step or add a new one. In most cases, selecting "Replace current" is the cleanest option.
6. Close and Apply: In the upper-left corner of the Power Query Editor, click the Close & Apply button. This will save your changes, close the editor, and apply the transformations to your data model. Your report visuals will refresh and display the sum correctly.

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 If My Numbers Won't Convert? Handling Dirty Data

Sometimes, Power BI resists changing a "Text" column to a "Number" because there's non-numeric text mixed in. A dollar sign ($), a comma (,), or stray words like "unavailable" in a few rows can disrupt the conversion for the entire column.

You can easily clean this up in the Power Query Editor.

Using "Replace Values" to Clean Data

If your numbers contain common symbols, like commas or currency symbols, the "Replace Values" function is your best friend.

1. In the Power Query Editor, right-click the header of the column you need to clean.
2. Select Replace Values from the context menu.
3. In the dialog box, enter the character you want to remove in the "Value To Find" box. For example, enter $. Leave the "Replace With" box empty.
4. Click "OK." The step will be applied. Repeat this process for any other characters like commas.
5. Once all non-numeric characters are removed, change the data type of the column as described in the previous section.

Correcting the Summarization Setting

Finally, there's the less common scenario where the data type is correct (it's already a number), but it still defaults to counting.

In the "Report" view, when you drag a numeric field into a value-based visual like a Card or the Values well of a bar chart, Power BI defaults to Sum. You can override this by clicking the little downward arrow next to the field name in the Visualizations pane and choosing another option like Average, Minimum, or Count.

However, you can also set the default behavior for the whole column. If it's acting strangely, it's worth checking this setting.

1. Go to the Data view or the Model view.
2. Select the column in question.
3. In the Column tools ribbon (or the Properties pane), look for a setting called Summarization or Default summarization.
4. Ensure this is set to Sum and not "Don't summarize" or "Count."

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

Nearly every time Power BI shows a count instead of a value, the root cause is a simple data type misunderstanding. By ensuring your numeric columns are correctly formatted as numbers - ideally as a permanent step in the Power Query Editor - you eliminate the issue at its source and set yourself up for reliable, accurate reporting that updates correctly every time.

Wrestling with data type corrections, cleaning steps, and summarization settings is a common part of the BI tool experience, it's the friction you have to overcome before you can get to the actual insights. At Graphed we felt this manual analytical busywork wasted too much time. That's why we built an AI data analyst that handles these data transformations automatically. You simply connect sources like Google Analytics or your CRM, then ask a question in plain English, and our tool generates live, interactive dashboards, ensuring metrics are summed correctly and letting you focus on the answers, not on configuring the setup.