How to Change Millions to Thousands in Power BI

When you're dealing with big numbers, a Power BI dashboard can quickly become cluttered with trailing zeros. A sales figure of $12,546,890.34 can look intimidating and is difficult to compare at a glance. Simplifying it to $12.5M makes the information instantly digestible. This article will show you three effective methods to format thousands (K), millions (M), or billions (B) in Power BI, ranging from a simple click to a powerful custom formula.

Why Is Number Formatting So Important in Your Reports?

Before jumping into the "how," let's explore the "why." Proper number formatting isn't just about aesthetics, it's about making data useful. Your dashboard’s job is to communicate key insights quickly. If a stakeholder has to squint and count digits to determine whether they're seeing ten million or one hundred million, you've already lost their attention.

Here are the key benefits:

• Improved Readability: The human brain processes "$1.5M" far faster than "$1,500,000." This clarity allows users to spot trends and outliers without getting bogged down in specifics.

• Better Use of Space: Dashboards have limited real estate. Shortened numbers like "1,234K" fit neatly into tables, charts, and cards, preventing visual clutter and ensuring your designs stay clean and professional.

• Clearer Communication: When presenting your report, dynamic formatting ensures you're telling a clear story. Large numbers are instantly contextualized, meaning your audience spends less time deciphering your visuals and more time understanding the insights you're sharing.

Method 1: Using the 'Display Units' Feature (The Easiest Way)

Power BI includes a built-in feature perfect for quick, on-the-fly formatting on a specific visual. If you need a fast and simple solution, this is your go-to method.

How To Do It: Step-by-Step

Let's say you have a Card visual showing "Total Revenue" as $5,280,110. Here's how to change it to $5.3M:

1. Select the chart, card, or table you want to adjust.

2. With the visual selected, navigate to the Visualizations pane on the right-hand side.

3. Click the Format your visual icon, which resembles a paintbrush.

4. Find the settings for the value you want to format. This will vary by visual:

   • For a Card, expand the Callout value section.

   • For a Bar or Line Chart, expand the Y-axis section, then the nested Values section.

   • For a Table or Matrix, look for the Values section.

5. Within these settings, you'll see a dropdown menu called Display units. It is likely set to "Auto" or "None."

6. Change this dropdown to Thousands, Millions, or Billions. Power BI will automatically handle the conversion and add the appropriate suffix (K, M, or B).

You can also control the number of decimal places shown by entering a number in the Value decimal places box, located next to the display units.

Pros and Cons

• Pros: It’s incredibly fast and requires zero coding. Anyone can do it in seconds. This is perfect for when you are building a draft or need a quick visual fix during a meeting.

• Cons: This method is not very flexible. The setting only applies to the single visual you've selected, not the underlying measure itself. If you use "Total Revenue" in five different charts, you have to repeat this process for all of them. Moreover, it applies a single unit across all data points in that visual, which can sometimes distort values at different scales.

Method 2: Applying a Custom Formatting String

If you need more control and consistency across your entire report, applying a custom format directly to your measure is the right approach. This method sets a default format for your measure, so every time you use it in any visual, it applies the exact same way.

Understanding Custom Format Strings

Power BI uses a system of format codes similar to Excel. By creating a specific string of these codes, you can tell Power BI exactly how you want a number to look. The magic lies in the commas. Each comma at the end of your format string tells Power BI to divide the number by 1,000.

• One Comma: Divides by 1,000 (Thousands). #,##0,"K"

• Two Commas: Divides by 1,000,000 (Millions). #,##0,,"M"

• Three Commas: Divides by 1,000,000,000 (Billions). #,##0,,, "B"

How To Do It: Step-by-Step

1. Go to either Report View or Model View.

2. In the Data pane on the right, find and select the measure or column you want to format (e.g., "Total Revenue").

3. This will bring up the Measure tools (or Column tools) menu in the ribbon at the top.

4. In the Formatting section of the ribbon, click the dropdown menu that currently says something like "Currency" or "General" and type in your custom format string.

5. Common examples you might use: #,##0.0,"M" to display in millions, #,##0.0,"K" to display in thousands. Adjust as necessary to fit your data display needs.

When and Why Use It

• Use Cases: This is a solid intermediate approach. It's great when a single format (like showing everything in millions with one decimal) works for your report's data scale. It standardizes the metric, which boosts consistency.

• Limitations: Like the first method, it's still static. If your "Total Revenue" measure occasionally includes smaller numbers, say $500, it would be displayed as "$0.0M", which is confusing and inaccurate. This makes it unsuitable for datasets with a wide range of values.

Method 3: Creating a Dynamic DAX Measure (The Ultimate Solution)

For complete control and a genuinely professional look, you need to turn to Data Analysis Expressions (DAX). Using a DAX measure, you can create a format that dynamically adjusts based on the actual values of your numbers. This method is the epitome of flexibility and adaptability.

Crafting a Dynamic DAX Measure

The goal is to create a measure that automatically adjusts the format to fit the number's scale. This ensures accuracy and readability across any data points varying in size. Here's how you can do it:

1. Create a new measure in your data model.

2. Use the following DAX formula as a template:

The Big Caveat: Text vs. Numbers

There's a critical detail to understand here: when the DAX FORMAT function is used, the output is text, not a numeric value. This is important when it comes to further calculations or aggregations, so plan accordingly.

Which Method Should You Use?

Choosing the right method depends on the nature of your data and your reporting needs:

• Use Display Units: Ideal for quick adjustments on a case-by-case basis when you need something fast and easy.

• Apply a Custom Formatting String: Best for consistency across reports when a static format is required.

• Create a Dynamic DAX Measure: The ultimate choice for flexibility and professional reporting, especially for varied datasets.

Final Thoughts

Effectively formatting large numbers transforms a complex Power BI report into an easy-to-read, high-impact dashboard. Each of the three methods has its place: start with the simple ‘Display units’ for quick jobs, use custom formatting strings for report-wide consistency, and create dynamic DAX measures for an intelligent, polished, and professional final product.

While mastering specific features in Power BI is a valuable skill, it's one of many granular steps that can consume your time. We built Graphed because we believe valuable insights shouldn't require an instruction manual. Instead of writing formulas or hunting through formatting panes, you can simply connect your data sources and ask questions like, "Show me my ad spend vs. revenue by campaign for last month." In seconds, we build a real-time, interactive dashboard for you, with the right formatting already handled. That means you can stay focused on actionable insights and make informed decisions without getting bogged down by technical details.