Strategic Approach to Formatting Values

 Summary

As with many Microsoft products, Power BI offers several ways to achieve the same outcome, and formatting values is no exception. Without a strategy, the variety of approaches can quickly make it a challenge to manage value formatting effectively. I use a scenario to show how formatting can quickly get out of control and suggest an approach for managing the situation.

 

 Scenario

1. You format the "Sales Amount" column as currency with two decimal places in Table View.

2. Then, you create a measure called "Total Sales" and format it as a whole number.

3. In a bar chart, you set the values to display in thousands (K) with one decimal place.

4. You format the data labels to show as currency with no decimals. 

5. The visualisation, and numerous others, look like this:

What went wrong?

By formatting in multiple locations, it's not clear where we are applying formatting, and each addition overwrites the intention of the previous action. Not only is there a clear impact to the end user, with confusing inconsistencies in how values are displayed, but it also makes it harder to manage the report; the need to update formatting in multiple places makes maintenance time-consuming and error prone.

You might look at the scenario and think that you'd never develop the report in that way. However, without a clear strategy for managing value formatting, a few updates over time could easily lead you into a similar situation.

 

 Solution

In the scenario, values are formatted in four locations, in the data label and y-axis sections of the formatting pane, as well as in the measure and column. To avoid issues with consistency and manageability, it's clear that formatting should be applied in just one location where possible.

The table below compares the different locations where value formatting can be applied. ‘On Visual’ refers to formatting that is applied to a visual using the format pane, such as y-axis values and data label values.

The table presents a strong case for formatting values in measures as much as possible. This can be applied in three ways:

• Out-of-the-box options: integer, currency, percentage etc.
• Static formatting strings: “+0d;-0d;0”, “mmm-yy” etc.
• Dynamic formatting strings: IF ( [#Videos] = 1 , “0 Video” , “0 Videos” ) etc.

If a measure can’t be created, such as x-axis values, formatting should be applied to a column in the table view of model view. Column formatting supports reusability and consistency but lacks the option for dynamic formatting.

Once value formatting has been applied to measures, ensure that the ‘Display units’ setting is set to ‘None’ on the values and data labels in visuals, this will prevent the ‘Auto’ function from negatively interacting with the formatting.

 

 Conclusion

Without a clear strategy for formatting, values are often formatted in different locations, such as the data label and y-axis sections of the formatting pane, as well as in the measure and column. This creates confusion, inconsistencies, and makes managing reports more difficult.

To avoid this, it is best to minimise on-visual adjustments and centralise formatting at the measure level wherever possible. If a measure can’t be created, such as for x-axis values, apply formatting to the column.

This strategy ensures better consistency, reusability, and flexibility. If you don’t already follow this approach, give it a try on your next report, I’m sure you’ll find that value formatting becomes easier to manage and maintain.

Thank you

Thanks for reading! Feel free to leave a comment if you have any questions or feedback.