What Is Dynamic Formatting in Power BI (And Why Your Big Numbers Might Be Lying to You)

If you have ever glanced at a Power BI card, seen “$6M,” and felt a small surge of pride before someone pulled up the underlying number and it was actually $5.56M, you already understand the problem dynamic formatting solves.

Power BI’s default display behavior, auto units, rounds values based on their magnitude so they fit neatly on a card or chart. Most of the time that is a convenience. At scale, it can quietly distort the number your stakeholders are making decisions on.

This post breaks down what dynamic formatting is, why the default formatting behavior in Power BI can create misleading reports, and how to write the DAX needed to fix it.

Why Does Power BI Round Numbers So Aggressively?

Power BI’s auto display units setting looks at the magnitude of a value and picks a unit, thousands, millions, billions, to keep the displayed number short. To do that, it typically shows only one or two significant digits.

That works fine in the thousands range, where rounding barely moves the number. It breaks down as values grow. A value of 5.56 million can display as “6M,” an overstatement of roughly 7%. Move into the hundreds of millions or billions, and that same rounding behavior can inflate or deflate a figure by tens of millions of dollars.

For a casual dashboard, that might not matter. For a report driving revenue targets, forecasting, or board-level decisions, a 7% gap between the displayed number and the real number is the kind of thing that erodes trust in the data, and sometimes changes the decision itself.

What Is Dynamic Formatting in Power BI?

Dynamic formatting is a feature that lets you write format instructions directly into a measure using DAX, rather than relying on Power BI’s built-in auto units setting. Instead of Power BI deciding how to round and display a value based on generic rules, you define exactly how that value should be displayed at every magnitude.

In practice, this means writing a switch statement inside the measure’s format expression that checks the size of the value and returns a specific display format for each range, thousands, tens of thousands, millions, tens of millions, and beyond. Power BI reads the magnitude of the value at render time and applies whichever formatting rule matches.

The result is a display that stays precise and consistent no matter how large the underlying number gets, without you having to manually adjust formatting every time the data changes.

How Does Dynamic Formatting Actually Work?

Dynamic formatting lives in the format section of a measure, not the value expression itself. The typical pattern looks like this:

1. Initialize a variable that takes the absolute value of the measure so the logic works regardless of sign.
2. Build a switch statement that evaluates the size of that value, starting with the smallest range and stepping up through each order of magnitude.
3. For each range, define the exact display format: how many decimal places to show, whether to include a currency symbol, and whether to add comma separators.
4. Let Power BI apply the matching rule automatically as the value changes, whether that is from a slicer selection, a filter, or new data loading in.

Because the formatting logic is tied to the measure rather than a visual-level setting, it travels with the measure everywhere it is used, cards, tables, charts, so formatting stays consistent across an entire report.

When Should You Use Dynamic Formatting Instead of Default Formatting?

Dynamic formatting earns its complexity in a few common scenarios:

• High-visibility KPI cards. Executive dashboards and scorecards are exactly where a rounded, overstated number does the most damage, since these are often the only figures decision-makers see.
• Wide-ranging values. If a measure can realistically span from the thousands to the billions depending on filters or time period, a single static format will look wrong at one end of that range or the other.
• Tight visual real estate. Cards and small visuals only have room for a handful of characters. Default currency or comma formatting can push a value past its container and get cut off, while dynamic formatting can be tuned to fit the available space at every scale.
• Financial and operational reporting. Anywhere a rounding gap could plausibly change a business conversation, revenue, pipeline, cost, is a candidate for dynamic formatting.

For lower-stakes or internal exploratory reports, Power BI’s default auto units setting is often good enough. Dynamic formatting is a deliberate trade of a bit of extra DAX for a meaningful gain in accuracy where it counts.

Is Dynamic Formatting Difficult to Set Up?

Not particularly, but it does require comfort writing DAX beyond a basic measure. The format logic itself is a straightforward switch statement, and once it is built for one measure, the same structure can typically be adapted and reused across other measures in the same model.

The harder part is usually not the syntax. It is knowing which ranges matter for your specific data, what level of precision your audience actually needs, and how to structure a semantic model so formatting logic like this is consistent and maintainable rather than duplicated across dozens of one-off measures. That governance layer, a clean, well-documented semantic model, is what makes techniques like dynamic formatting scale across a report or an entire organization instead of living as a one-off trick.

See It in Action

A Collectiv Senior Analytics Consultant walks through dynamic formatting live inside a Power BI report, comparing default auto formatting against dynamic formatting on the same values, and showing exactly where the two diverge as numbers grow.

How Collectiv Can Help

Dynamic formatting is a small technique that points to a bigger question: is your Power BI environment built on a semantic model that can support accurate, consistent reporting as your data scales? Collectiv helps organizations design, optimize, and train teams on Power BI environments that get the fundamentals right, from semantic modeling to report-level polish like this.

Whether your team needs hands-on Power BI training or a broader assessment of your reporting environment, Collectiv can help.

Explore Power BI Training | Explore Power BI Consulting

Have a Power BI formatting or reporting challenge you’re trying to solve? Talk to us today.

Frequently Asked Questions

What is dynamic formatting in Power BI?

Dynamic formatting is a technique that uses DAX written inside a measure’s format expression to control exactly how that measure displays at different magnitudes, replacing Power BI’s default auto units rounding behavior.

Why does Power BI show inaccurate numbers on cards?

Power BI’s default auto display units setting rounds values to fit a short display, often showing only one or two significant digits. At large magnitudes, that rounding can create a meaningful gap between the displayed number and the actual value.

Do I need DAX experience to use dynamic formatting?

Yes, dynamic formatting requires writing a switch statement in DAX within the measure’s format section. It is more advanced than adjusting a formatting dropdown, but it follows a repeatable pattern that can be reused across measures.

Does dynamic formatting affect the underlying data?

No. Dynamic formatting only changes how a value is displayed. The underlying value in the model remains unchanged and available for accurate calculations, filtering, and export.

Where should I use dynamic formatting first?

Start with the KPI cards and summary visuals your stakeholders look at most, especially where values span a wide range or where a rounding error could influence a business decision.