Executive Summary One of the most common friction points in BI reporting is number scaling. Executives often prefer high-level views in Millions or Billions to spot trends, while analysts need Actual values to reconcile data or investigate anomalies.
Power BI’s default visual settings force us to choose: stick with "Auto" (which offers no user control) or hardcode display units to K, M, or B (which forces a single scale for everyone).
In this post, I’ll show you how to use Dynamic Format Strings to give your users full, interactive control over how they view their data—without duplicating measures or cluttering your report.
The Problem: The "One-Size-Fits-None" Format
Typically, we rely on the Display units setting in the visual options. While functional, it has significant limitations:
- Auto formatting is unpredictable and lacks user control.
- Fixed units (e.g., Millions) might look great for a full-year total but render smaller monthly values as $0.0M.
- Duplicate Measures (e.g., "Sales (M)" vs "Sales (Actual)") create technical debt and confuse self-service users.
The Solution: User-Driven Dynamic Formatting
By leveraging Dynamic Format Strings, we can change how a measure looks without changing the underlying calculation. This allows a single measure to toggle between Actuals, Thousands, Millions, and Billions based on a simple slicer selection.
Here is the step-by-step implementation guide.
Step 1: Create a Scale Dimension Table
First, we need a disconnected table to feed our slicer. This table defines the scale options and an ID for sorting. You can create this directly in DAX:
Scale =
DATATABLE
(
"Scale Name", STRING,
"Scale ID", INTEGER,
{
{ "Actuals", 1 },
{ "Thousands", 2 },
{ "Millions", 3 },
{ "Billions", 4 }
}
)
Tip: Sort the 'Scale Name' column by 'Scale ID' to ensure the slicer options appear in a logical order (Smallest to Largest) rather than alphabetically.
Control Scale Table
Step 2: Capture the User’s Selection
Next, create a measure to read the slicer selection. This will drive our formatting logic:
Selected Scale = SELECTEDVALUE ( 'Scale'[Scale Name] )Step 3: Create Your Base Measure
Write your KPI measure as you normally would. Do not divide it by 1000 or 1,000,000 inside the DAX logic. Keep the number whole.
Total Profit = SUM ( Financials[Profit] )Step 4: Apply the Dynamic Format String
This is where the magic happens.
- Select your measure (Total Profit) in the Data pane.
- In the Measure Tools ribbon, change the Format dropdown from General (or Currency) to Dynamic.
- A new DAX formula bar will appear for the format string. Paste in the following logic:
SWITCH (
[Selected Scale],
"Thousands", "$#,##0,.00",
"Millions", "$#,##0,,.00",
"Billions", "$#,##0,,,.00",
"Actuals", "$#,##0.00",
"$#,##0,,.00" -- Default fallback (e.g., Millions)
)
Format : Dynamic
Note the comma placement: ,.00 divides by a thousand, ,,.00 divides by a million, etc.
Why This Matters
This technique offers immediate benefits for both developers and end-users:
- Improved UX: Users get a self-service experience where they control the granularity of the data.
- Cleaner Semantic Models: You no longer need separate measures for "Sales (M)" and "Sales (k)," reducing technical debt.
- Consistency: The underlying calculation remains the same across all visuals, ensuring data integrity.
Showing in Actual Dollars
Showing in Thousands
Showing in Millions
Bonus Use Case: Multi-Currency Reporting
You can extend this logic beyond just scaling numbers. This same pattern works perfectly for Multi-Currency Reporting. By creating a Currency table (USD, EUR, GBP) and using a similar SWITCH statement, you can dynamically change the currency symbol (e.g., $ vs €) based on user selection, all within a single measure.
Final Thoughts
Dynamic format strings are a powerful, often underused feature in Power BI. By combining them with a simple parameter table, you can bridge the gap between executive summaries and analyst-level detail, delivering a truly modern and flexible BI experience.
Super User
