Dynamic Formatting in Power BI – Simplified for Everyone

Struggling with number formatting in Power BI?

We’ve all been there. You create a great-looking dashboard, but your numbers just don’t feel right:

• Some values need to be show in lakhs (₹ 10 L) but there is no such inbuilt format.

• Others need to switch formats based on the data type or context

If this sounds familiar, we’ve got you covered!

Problem Statement

In Power BI dashboards, formatting numbers correctly is often challenging. Common issues include:

• You want values to show in lakhs (₹ 10 L) or crores (₹ 1 Cr), but Power BI doesn’t offer a direct format.

• You want different measures like Sales, Profit, and Growth % to appear in a single column with different formats.

• Even if you create multiple measures, a table or matrix column can only display one measure at a time—so formatting breaks.

 

Solution – Dynamic Formatting

Dynamic formatting allows you to control the number format of a measure based on conditions. Instead of separate measures you create one measure that switches formats automatically.

Benefits:

1. Cleaner dashboards – fewer redundant measures

2. Flexible – works in table or matrix where multiple metrics are needed in one column

3. Professional – each value shows in its correct format automatically

What is Dynamic Formatting in Power BI?

Dynamic formatting allows your measure values to change their display format automatically based on conditions you define.

Instead of creating multiple measures for different formats like:

• Sales → ₹ 50 L

• Profit → ₹ 3 Cr

• Growth → 12.5%

You can create one measure that automatically switches between formats such as currency, percentage, lakhs, or crores depending on the data context.

In this blog, I’ll show you two practical cases of dynamic formatting in Power BI:

1. Simple formatting with one format type

2. Advanced formatting with multiple formats dynamically

Case 1: Simple Formatting (Single Format)

Sometimes, you just want all your numbers to appear in a single, clear format—like Lakhs (L).

Example format: " ₹ #0 L;  ₹ (#0 L)"

This will:

• Show positive numbers as ₹ 50 L

• Show negative numbers in parentheses as ₹ (50 L)

Step 1: Create Your Measure

Let’s say you have a measure called Amount.

Create a new measure like this:(to calculate the desired value in lakhs) Amount in Lac =

DIVIDE (

SUM ('Fact - SIL' [GST Base Amount]), 100000, )

Step 2: Apply Format in Measure

Now, format the measure:

1. Go to the Measure in the Fields pane.

2. Under Format, choose Dynamic Format and type: ₹ #0 L;₹ (#0 L)

That’s it! Your measure will now display in Lakhs everywhere you use it.

Tip: This approach is perfect for dashboards where you always want a single, consistent format & there is no inbuilt option for the desired format.

Case 2: Dynamic Formatting (Multiple Formats)

This is the real game-changer when:

• You have a matrix where Sales, Profit, and Growth % appear in the same column.

• Different measures require different formats.

• Creating separate measures does not work because a single matrix column can’t switch formats automatically

Step 1: Create a Format Column

In your data model, create a calculated column that defines which format each row should use.

Step 2: Create a Dynamic Measure

1. Create the normal measure that gives you the desired result.

   
   

   For example, here

   

   
   

2. Go to the Measure in the Fields pane.

3. Under Format, choose Dynamic Format and type:

4. Now click Edit in DAX formula bar under Format String and define your desired format.

   
   

   For example, here

   

Step 3: Use in Visuals

• Drag your Dynamic Value measure into your visual.

• Power BI will automatically show each metric in its proper format.

Example Output:

Known Limitations

Dynamic formatting is powerful, but not perfect:

1. Totals Formatting Issue – The total row cannot be dynamically formatted; it will follow one default format.

2. Limited Visual Support – Works best in tables, matrices, and cards. Some visuals (like charts) may ignore dynamic formatting.

3. Performance Impact – Complex dynamic measures in large datasets can slow performance.

When to Use Which

• Case 1: When all values follow one format and there is no inbuilt option for the desired format. (e.g., always in Lakhs).

• Case 2: When you need multiple metrics in the same column with different formats.

Conclusion

Dynamic formatting in Power BI is a powerful technique to make dashboards cleaner, smarter, and more professional.

• Single-format measures are best for consistent dashboards.

• Dynamic measures shine when different metrics need different formats in the same visual, especially in table and matrix columns.

While totals and some visuals still have limitations, using dynamic format strings can drastically reduce redundant measures and elevate your dashboard quality.

Once you implement this, manual formatting will be a thing of the past.