Dynamic measure formatting allows you to change how a measure is displayed based on its value or user selections. This can make Power BI reports easier to read while reducing the need to create and maintain separate reports for different formatting requirements.
Understanding display formatting
Large numbers can be difficult to interpret at a glance. Power BI lets you set display units on a visual to automatically represent values as thousands, millions, billions, or trillions. The challenge with static display formatting is that you must choose one format for the visual. If your dataset contains numbers at very different scales, selecting millions may make smaller values less useful, while selecting trillions may make most of the dataset irrelevant. Dynamic formatting solves this problem by allowing the format to change according to the value of the measure.
Dynamically formatting large numbers
Start by creating a basic measure containing the value you want to format. Then, under the measure's formatting options, select the dynamic formatting option. A formatting expression can use the measure's value as a variable and a SWITCH statement to determine which format string should be applied. For example, you can create conditions that display:
- Values up to 1,000 as the original number
- Values up to 1 million in thousands using K
- Values up to 1 billion in millions using M
- Values up to 1 trillion in billions
- Larger values in trillions using T
This allows a single measure to display numbers at an appropriate scale automatically, making it much easier to understand the relative size of each number at a glance.
Working with format strings
Format strings determine how the underlying value appears. A # displays a digit when one is present without forcing a value into an empty position. A 0, on the other hand, displays zero when the corresponding digit is blank. This makes zeros useful when you want to enforce decimal places, leading zeros, or trailing zeros. Commas can be used within the format string to scale large numbers, while decimal points can control the number of displayed decimal places. You can also add characters such as K, M, or T to communicate the scale of the resulting value.
Letting users choose their date format
Dynamic formatting can also make a report more useful for people working in different regions. For example, employees around the world may prefer different date formats, including:
- Month, date, year
- Year, month, date
- Date, month, year
Instead of maintaining separate versions of a report, you can create a simple control table containing the available date formats. A slicer connected to that table lets the user choose the format they prefer. A measure can then retrieve the selected format string and dynamically apply it to the displayed date. This gives multiple audiences a report that matches the date convention they are accustomed to using.
Switching between hours and revenue
Dynamic formatting becomes especially helpful when the same report needs to meet the needs of different audiences. Consider an operations report showing billable time for technicians and the projects they worked on. An operations manager may want to see hours, while someone in finance may want to see the revenue generated by each technician. Another manager may need access to both. Rather than creating entirely separate reports, you can create a control table and slicer that allow users to switch between time and monetary value. A measure can use a SWITCH statement to determine which value to return:
- When the user selects time, return the sum of labor hours.
- When the user selects value, return the sum of price.
The same measure can then be used throughout the report's visuals, allowing one slicer to change what those visuals display.
Dynamically formatting the selected measure
Changing the underlying value is only part of the solution. Hours and currency need different formats. The logic used to switch between the measures can also be reused in the dynamic formatting expression. For hours, the format can display the value with the desired decimal places. For revenue, the format string can include a dollar sign, thousands separator, and two decimal places for cents. Formatting strings can also define how negative values appear. For example, a negative monetary value can use the same currency format while displaying the amount inside parentheses. A separate format can also be specified for zero values when needed.
Why dynamic formatting is useful
Dynamic formatting lets one Power BI report adapt to different values, audiences, and reporting requirements without requiring several separate reports. You can use it to:
- Make large numbers easier to interpret.
- Automatically adjust formatting based on the size of a value.
- Let users select the date format they prefer.
- Switch report visuals between measures such as hours and revenue.
- Apply the correct formatting whenever the underlying measure changes.
- Reduce the number of separate reports that need to be built and maintained.
