How to Use Dynamic Format in Power BI

Tired of creating multiple measures just to show a value as a currency in one chart and a percentage in another? Power BI's dynamic format strings can solve this, letting you build flexible, user-friendly reports where the formatting changes automatically based on context. This tutorial breaks down how to use dynamic formatting to streamline your dashboard development and impress your users.

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 Are Dynamic Format Strings in Power BI?

Normally, when you format a measure in Power BI, you choose a single, static format. For example, you might set a sales measure to always display as U.S. Dollars ($1,234.56) or a growth metric to always be a percentage (15.2%). This format never changes.

A dynamic format string lets you use a DAX expression to define the format of a measure on the fly. This means the way a number is displayed can change based on a user's slicer selection, the value of the number itself, or other conditions within your report. It turns one-dimensional measures into multi-faceted analytical tools without cluttering your data model with duplicates.

Why Use Dynamic Formatting?

Before diving into the "how," let's cover the "why." Embracing dynamic formats offers several clear benefits for both you as a developer and for your end-users.

• Better User Experience: People understand data better when it's presented in a familiar context. Letting finance team members see numbers with currency symbols while marketing sees the same figures as a percentage of a goal makes the report more intuitive for everyone. It feels slick, modern, and built around their needs.
• Simpler Data Model: The traditional way to handle multiple formats was to create multiple measures. For instance, you’d have [TotalSalesUSD], [TotalSalesEUR], and [TotalSalesGBP]. With dynamic formatting, you only need one measure: [TotalSales]. This keeps your model clean, lean, and much easier to manage.
• Increased Report Flexibility: A single visual can tell multiple stories. Imagine a manager wants to see total revenue but then instantly switch the view to see what percentage each product contributes to that total. Dynamic formatting makes this possible with just one chart and a simple slicer.

How to Implement Dynamic Formatting: A Step-by-Step Guide

Let's build a practical example from scratch. Our goal is to create a single measure that can display total sales as a whole number, a currency value, or a percentage of the grand total, based on a user's selection from a slicer.

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

Step 1: Create a Base Measure

First, you need a standard measure to work with. For this example, let's assume you have a Sales table with a Revenue column. In Power BI Desktop, create a new measure with the following DAX formula:

Total Sales = SUM(Sales[Revenue])

This is our starting point - a simple, unformatted sum of revenue.

Step 2: Create a Disconnected Table for User Selection

To give an end user control, you need a way for them to make a selection. We’ll build a small, disconnected table that isn't connected to any other tables in your model. This table will simply hold a list of the format options we want to offer.

1. On the Home tab, click Enter data.
2. A dialog box will appear. Create two columns: "Format Option" and "Sort Order".
3. Enter the options you want to display in your slicer. The "Sort Order" column ensures the options appear in a logical order, not just alphabetically.

Here’s what your table should look like:

Give your table a descriptive name like "Format Selector" and click Load.

Now, go to your report canvas and add a slicer using the "Format Option" column from your new Format Selector table.

Step 3: Create the Reporting Measure with Dynamic Logic

Now, we create the new measure that will handle all the logic. This measure will check which option is selected in the slicer and perform the correct calculation.

Create a new measure with this DAX snippet:

Sales Formatted = 
VAR SelectedFormat = SELECTEDVALUE('Format Selector'[Format Option], "Currency") // Default to Currency
RETURN
SWITCH(
    TRUE(),
    SelectedFormat = "Percentage of Total", DIVIDE(
        [Total Sales],
        CALCULATE([Total Sales], ALL('Format Selector'))
    ),
    // For "Currency" and "Number", just return the base measure. The magic happens in the format string.
    [Total Sales]
)

Let's break this down:

• The SELECTEDVALUE function grabs the current selection from our slicer. If nothing is selected, it defaults to "Currency".
• The SWITCH(TRUE(), ...) statement checks the selection.
• If "Percentage of Total" is selected, it calculates the percentage. Otherwise, it simply returns the original [Total Sales] value.

At this point, the value is dynamic, but the format is not. That's the final and most important step.

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 4: Apply the Dynamic Format String

This is where the magic happens. We’ll tell Power BI how to format the [Sales Formatted] measure based on the same slicer selection.

1. In the Data pane on the right, find and select your Sales Formatted measure.
2. This will open the Measure tools context tab on the ribbon at the top.
3. In the "Formatting" section of the ribbon, click the dropdown menu that currently says "General" or another format.
4. At the bottom of this dropdown, select Dynamic.

A formula bar will appear above your canvas, showing "FORMAT" on the left. This is where you write the DAX that returns the format code as a text string.

Enter the following DAX formula:

VAR SelectedFormat = SELECTEDVALUE('Format Selector'[Format Option], "Currency")
RETURN
SWITCH(
    TRUE(),
    SelectedFormat = "Currency", "$#,##0", // Currency format
    SelectedFormat = "Number", "#,##0",     // Whole number format with comma
    SelectedFormat = "Percentage of Total", "0.0%", // Percentage format
    "$#,##0" // Default format
)

Just like that, your measure is now truly dynamic. Drag the Sales Formatted measure into a card visual. Now, when you click the options in your slicer ("Currency", "Number", "Percentage of Total"), both the value and the display format of the card will change instantly.

More Practical Use Cases

Once you understand the concept, you can apply it to many other scenarios.

Scaling Numbers (K/M/B)

It’s often cleaner to display large numbers like $15,200,000 as "$15.2M". Instead of using a slicer, the format can be determined by the value of the measure itself.

For your measure (e.g., [Total Sales]), set the format to Dynamic and use this DAX in the format string bar:

SWITCH(
    TRUE(),
    [Total Sales] >= 1000000000, "#,##0,,,.0B",
    [Total Sales] >= 1000000, "#,##0,,.0M",
    [Total Sales] >= 1000, "#,##0,.0K",
    "#,##0"
)

In DAX format strings, each comma at the end of the number placeholder divides the number by 1000. So ,, divides by one million, and ,,, divides by one billion. It's a clean and efficient way to scale your numbers automatically.

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

Conditional Formatting for Positive and Negative Values

Accountants often require negative numbers to be displayed in parentheses. A standard format string is structured in sections separated by semicolons: positive format,negative format,zero format.

To achieve this, you don't even need DAX. Just find your measure, and in the regular format box, type this custom format string:

$#,##0,($#,##0),$0

This will automatically format positive values like $1,200, negative values like ($1,200), and zero as $0. You can apply this same string dynamically if other conditions need to be met.

Tips and Best Practices

• Model View is Your Friend: You can select multiple measures at once in the Model view and switch them all to Dynamic formatting, saving you clicks.
• Test Your Format Strings: A common error is a small typo in the format code that DAX doesn't recognize (e.g., #,##a instead of #,##0). Always double-check them.
• Calculation Groups Precedence: Be aware that if your data model uses Calculation Groups, their formatting rules can override the dynamic format strings set on a measure.
• Performance Considerations: The DAX for the dynamic format is executed for every single data point in a visual, so it can have a light performance impact. Use it where it adds significant UX value, but avoid applying complex logic to every measure in a massive table.

Final Thoughts

Dynamic format strings are a fantastic tool in the Power BI toolkit. They empower you to create much cleaner data models and deliver more flexible, intuitive reports for your audience, bridging the gap between raw data and meaningful insights.

Building these user-friendly reports in tools like Power BI can be deeply rewarding but often takes a lot of manual setup and DAX knowledge. At Graphed, we're simplifying this entire process. Instead of writing formulas and configuring visuals, you can just connect your data sources - like Google Analytics, Shopify, or Salesforce - and describe the dashboard you want in plain English. We turn hours of report-building into a few seconds, generating real-time, interactive dashboards that answer your key business questions instantly.