Dynamic Number Formatting in Power BI with DAX

Dynamic number formatting feature allows you to conditionally format numbers based on the data context, making your reports more intuitive and user-friendly.

Dynamic number formatting enables you to apply different format strings to a measure depending on the filter context or the value of the measure itself. Unlike the static format strings, which remain constant, dynamic format strings can change, providing a more intuitive and meaningful representation of data

How to Enable Dynamic Number Formatting

1. Enable the Feature: Go to File > Options and settings > Options > Preview features and turn on "Dynamic formatting for measures"

How to use Dynamic Number Formatting

1. Select a measure that calculate numeric result.
2. In the Measure tools ribbon, expand the Format list box and select “Dynamic”

3. A new list box will appear to the left of the DAX formula bar.

4. Once the feature is enabled, you can write DAX expressions to define the format strings.

Here are a couple of examples:

Currency Formatting

VAR BaseFormatString = "#0,0.00"
VAR CurrSymbol = SELECTEDVALUE(Sales[Currency Code], "***")
VAR FormatString = BaseFormatString & " (" & CurrSymbol & ")"
RETURN FormatString


#This expression dynamically adds the currency symbol to the sales amount based on the currency code in the Sales table        

Scaling Large Numbers

VAR CurrentValue = SELECTEDMEASURE()
RETURN SWITCH(
    TRUE(),
    CurrentValue <= 1E3, "#,0.00",
    CurrentValue <= 1E6, "#,0,.00 K",
    CurrentValue <= 1E9, "#,0,,.00 M",
    CurrentValue <= 1E12, "#,0,,,.00 G"
)

#This format string scales large numbers using K for thousands, M for millions, and G for billions, making the data more readable        

Benefits of Dynamic Number Formatting

• The format adapts based on the data context, making reports more intuitive
• Large numbers are scaled appropriately, reducing clutter and improving comprehension
• Different formats can be applied to the same measure in different contexts, providing a tailored view of the data

By leveraging this feature, you can ensure that your data is presented in the most meaningful way possible, enhancing both the functionality and aesthetics of your Power BI dashboards.