Power BI Blog: Conditionally Formatting Brackets with Negatives

Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau takes a look at how to apply conditional formatting – specifically including brackets – to negative field values.

A commonly requested formatting style by accountants is to have negative numbers encapsulated with brackets in the report. Power BI inherently does not have such a formatting style. Therefore, we are going to have to be a little creative if we want to achieve the bracketed look.

Here is our dataset for this week:

Bringing in this data into Power BI and creating measures to calculate the total profit, we can then create the following visualisation:

We can see that for some days in January we have a negative profit amount (a loss), and we want to be able to display negative values with brackets around them.  The trick here is to use the FORMAT function.  This function has the following syntax:

where:

• the value parameter is the measure or field that we want to format
• the format_string parameter is the format style that we want to apply to the field or measure.

The format_string argument accepts code similar to the custom number formatting codes in Excel:

With this knowledge borne in mind, let’s create the following measure:

The ‘#,###’ in the first section represents the format we want to apply to positive numbers, the next section separated by the semi-colon ‘(#,###)’, is the code that we want to apply to negative numbers.  The formatted measure now looks like this: 

Success! Power BI now displays negative numbers with brackets around the value. Let’s fix the alignment so that the values are right justified. Select the visualisation, click on the Format tab under the Visualizations area, scroll down to ‘Field Formatting’. Here, select the ‘(Total Profit) Formatted’ measure and then select ‘Right’ in the Alignment options:

That’s how we format negative numbers with brackets in Power BI!

An important thing to note is that Power BI has styled the values as text and not numbers. Therefore, any calculations or measures built from this ‘(Total Profit) Formatted’ should be performed with the original measure ‘Total Profit’.

That’s it for this week, come back next week for more on Power BI.

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.