What is Fixed Decimal Number in Power BI?

If you’ve ever built a financial report in Power BI and noticed your grand totals are off by a few cents, you've likely encountered the subtle but frustrating quirks of data types. It’s a common issue where numbers that should be simple, like $19.99, get stored oddly and throw off your calculations. This article will show you how to solve this for good using the Fixed Decimal Number data type, explaining what it is, why it's essential for financial data, and exactly how to use it.

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 Exactly Is a Fixed Decimal Number in Power BI?

In Power BI, you have a few options for numerical data, but the two main ones are Decimal Number and Fixed Decimal Number. At first glance, they seem identical, but how they store information behind the scenes is completely different.

• Decimal Number: This is a "floating-point" number. It’s designed to be incredibly flexible and can handle a massive range of values, from enormous scientific figures to tiny fractions. However, it achieves this flexibility by sometimes storing numbers as extremely close approximations. For most calculations, this is perfectly fine.
• Fixed Decimal Number: This is, as the name suggests, fixed. It always maintains a precision of four digits to the right of the decimal point. It handles fewer total digits than its floating-point cousin (19 digits in total), but every one of them is precise. This data type is internally treated more like a large integer, which completely sidesteps the approximation issues of floating-point math.

Here’s a simple way to think about it:

Why Does This Matter? The Case for Fixed Decimals

So, a number might be off by 0.00000000001. Who cares? Well, your finance department does. When you start adding up thousands of transactions, those tiny, invisible errors can compound into a noticeable discrepancy. Imagine explaining to your boss why the revenue total in your Power BI dashboard doesn't match the accounting software — all because of a data type 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

The Telltale Sign of Floating-Point Errors

Let's say you have a sales table with product prices and quantities sold. You add a 'Total Sales' column in Power Query by multiplying '[Price]' by '[Quantity]'. If '[Price]' is a standard Decimal Number, you might get something like this:

(Price) $19.95 * (Quantity) 5 = (Total Sales) $99.7499999999999

When you display this in a Power BI table, it will probably round correctly and show "$99.75". Everything looks fine. But when you create a DAX measure to 'SUM(Sales[Total Sales])' for the whole year, the calculation uses the real underlying value, not the formatted one. After summing thousands of these tiny errors, your grand total might be $1,250,345.12 instead of the correct $1,250,347.88. This is the exact problem the Fixed Decimal type was designed to prevent.

Top Use Cases for Fixed Decimal Numbers

You should switch to the Fixed Decimal Number data type anytime accuracy is non-negotiable. Here are the most common scenarios:

• Financial Data: This is the number one reason. If a column contains revenue, costs, expenses, salaries, or prices, make it a Fixed Decimal.
• Transactional Data: Individual line items on an invoice, sales records from an e-commerce platform like Shopify, or payment records from Stripe must be precise.
• Currency Exchange Rates: When converting money, small rounding errors can become significant, especially with large volumes. Use Fixed Decimal for both the currency value and the exchange rate.
• Tax and Commission Calculations: Percentages used for calculating taxes or sales commissions demand accuracy.

How to Change Your Data Type to Fixed Decimal

Switching your column to the correct data type is easy, but it's best to do it as early as possible in your data preparation process. The ideal place is the Power Query Editor.

Method 1: In the Power Query Editor (Recommended)

Making this change in Power Query ensures that all subsequent calculations, both in Power Query itself and later in your DAX measures, are based on the correct, precise values.

1. From Power BI Desktop, click on "Transform data" in the Home ribbon. This will open the Power Query Editor.
2. In the left panel, select the query (the table) that contains the column you want to change.
3. Find the column in the data preview. Click on the data type icon at the top left of the column header (it probably looks like '1.2' for Decimal, '123' for Integer, or 'ABC' for Text).
4. From the dropdown menu, select Fixed Decimal Number. Power BI will then ask if you want to replace the current step or add a new step. It’s generally best to select "Replace current step" if the previous step was an automatic "Changed Type" step set by Power BI. Otherwise, "Add new step" is fine.
5. Once you're done, click "Close & Apply" in the upper left corner to return to your main Power BI window.

That's it! Your column now correctly stores the numeric data as a fixed-point decimal, protecting it from floating-point errors.

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

Method 2: In the Report or Data View

You can also change the data type directly from Power BI’s main interface, though it's generally better to do this in Power Query. This method is useful for a quick change if you've already loaded the data.

1. Click on the Data view icon on the left-hand navigation pane.
2. Select the table you want to modify from the Fields pane on the right.
3. Click on the column header you want to change in the data grid.
4. This will bring up the Column tools tab in the ribbon at the top of the screen.
5. In the "Formatting" section, find the Data type dropdown, and change it from "Decimal number" to "Fixed decimal number".

Potential Gotchas and Best Practices

While switching to Fixed Decimal is powerful, there are a few things to keep in mind to use it effectively.

Formatting vs. Data Type

One of the most common points of confusion for new Power BI users is the difference between formatting a column and changing its data type.

• Formatting: Changes how a number looks. Setting a standard decimal column to display as "Currency" with two decimal places only affects its visual appearance. The underlying, error-prone floating-point value is still there.
• Data Type: Changes how a number is stored and calculated. Changing the type to Fixed Decimal fixes the precision at its core.

Always fix the data type first, then apply the visual formatting you need (like adding a dollar sign or limiting visible decimal places).

Use a Relevant DAX Data Type

When you're writing DAX measures, understanding data types remains important. The DAX equivalent of the Fixed Decimal type is the Currency data type. While DAX often handles conversions automatically, if you're ever defining variables or calculating columns with DAX, using functions that output a currency type will help maintain that four-decimal precision. Many standard aggregations, like SUM(), on a Fixed Decimal column will correctly result in a Currency type.

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

Performance Considerations

Because Fixed Decimal types require more overhead than simple integers or standard floating-point decimals, they can be marginally slower in very large datasets (think hundreds of millions of rows). The impact is usually negligible, but it's a good practice to be intentional. Use it only where precision is non-negotiable — for financial data — and stick to standard decimals or whole numbers for things like sensor readings, counts, or other non-financial measurements.

Final Thoughts

Choosing the right data type is a fundamental step in creating reliable and accurate Power BI reports. By using the Fixed Decimal Number type for any and all financial data, you can build dashboards your team can trust, eliminating the confusing rounding errors that undermine confidence in your numbers.

We know that managing model details like data types, relationships, and queries in complex tools like Power BI can be a huge time sink, especially for marketers and sales teams who just need answers. We built Graphed to create an entirely different experience — one where a powerful AI analyst handles all the technical complexity for you. Instead of worrying about data types, you just connect your sources like Shopify, Google Analytics, Salesforce, or Stripe, and ask questions in plain English like "what was our sales revenue by product category last quarter?". The platform automatically generates accurate charts and live dashboards, giving you precise answers without ever needing to open a query editor.