As a qualified accountant, I often think about Power BI in terms of financial reporting. When working in this area, the standard method is to show negative numbers in brackets (or parentheses, as you may call them in your part of the world).
There is a currency number format in Power BI, but this doesn’t always what we want (more on that later). So, in this post we are looking at the correct way to show negative numbers in brackets.
Table of contents
- Where to format numbers
- Method #1: Currency format
- Method #2: Custom number format
- Method #3: FORMAT function
- Comparing methods
Where to format numbers
There are two places in Power BI where we can format numbers:
- Formatting applied to measures
- Formatting applied through the FORMAT function inside a measure
You’ll notice I didn’t include any Power Query options. That’s because setting the data type in Power Query impacts calculations, but not how the result is displayed on the Power BI canvas.
Now that’s established, let’s take a look at 3 methods for applying finance number formatting.
Method #1: Currency format
The most obvious option is applying the Currency number format to the measure.
- Select the measure [1]
- Select Currency from the Format drop-down [2]
- Click the $ drop-down [3] and select $ English (United States) [4]
Selecting a US currency format displays negative numbers in brackets and a currency symbol. We can also easily adjust the decimal places by clicking on the decimal spinner.
Sounds simple… right? So, what’s the problem?
The problem with the currency format
The trouble comes when switching to different countries, as the formats change. For example, switching from $ English (United States) to £ English (United Kingdom) removes the brackets (that we want) and applies a preceding minus sign (that we don’t) – D’oh!
The screenshot below compares the US and UK currency formatting. The change in brackets around negative numbers is easy to spot.
Secondly, the US formatting displays the $ symbol in front of the number, which isn’t what we want:
- This won’t work for anybody outside of the US.
- It is poor presentation based on the data-ink ratio.
Finally, even if we are in the US, the brackets are in the wrong place. The digits should be right aligned with each other, but the bracket should be excluded from the alignment.
Look at the screenshot below.
- The example on the left shows the bracket included in the alignment (this is wrong).
- The example on the right shows the bracket is outside of the alignment (this is correct).
Hopefully you’ll agree that Currency formatting seems like the obvious choice, but it has many issues.
Method #2: Custom number format
As the standard currency settings aren’t working for us, let’s turn to a custom number format.
Custom formats can be entered directly into the format box. As the format box has a drop-down icon, it may appear we can only select from the list. But this is not the case. We can type the number format directly into the box.
The format we want to apply is:
#,##0 ;(#,##0);- To apply this format, select the measure [1] enter the custom number format into the format box [2].
It will look like this:
There is an IMPORTANT point to be aware of. To create the correct alignment of digits, we use the non-breaking space character (known as nbsp). Power BI ignores standard spaces for alignment but recognizes the nbsp.
For zero, “-” is displayed. To ensure the correct alignment, a nbsp is included after the “-” symbol; you can’t see it in the screenshot above, but it is there (I promise).
TIP:
The non-breaking space has the code character code 160. The easiest way to apply this character inside Power BI is:
- In Excel, enter the following function into a cell: =CHAR(160)
- Copy the cell with the formula, then Paste Special > Values.
- Click into the cell with the pasted value and copy the nbsp character.
- Paste the character into the Power BI number format at the relevant point.
The nbsp character is useful in this circumstance but can be disastrous if included within our source data. Read this post to find out how to remove nbsp from the data in Power Query.
This is perfect if we use a mono-spaced font where each character has the same width (e.g., Courier). But we rarely use mono-spaced fonts; they are not the most practical. Depending on your font selection, the nbsp might be wider or thinner than a close bracket character. Therefore, this may not be pixel perfect, but its should be pretty close.
Alternative custom number formats
The screenshot below shows different number formats all based on the same source data. Notice how applying different number formats which can change the rounding and decimal places.
The codes used in the screenshots above are:
Example #1 - 2 Decimal places:
#,##0.00 ;(#,##0.00);-
Example #2 - Rounded thousands:
#,##0,. ;(#,##0,.);-
Example #3 - Rounded thousands and 1 decimal place:
#,##0,.0 ;(#,##0,.0);- Note: In all the formats above, the spaces are nbsp characters to ensure the correct alignment of digits.
Method #3: FORMAT function
The final number formatting option uses the FORMAT function.
The FORMAT function accepts a single value and returns that value in the specified format as text. It is commonly used to apply different number formats within a single column.
The syntax of the FORMAT function is:
FORMAT(<value>, <format_string>[, <locale_name>])- <value>: A value or a formula that returns a single value.
- <format_string>: The formatting code as a string (see examples in the section above)
- [locale_name]: An optional argument. It is the name of the locale to be used by the function. This is an advanced argument which is out of scope for this post.
The following DAX measure uses the FORMAT function and returns a number with negative values in brackets.
Total Value FORMAT =
FORMAT (
SUM(Data[Value] ),
"#,##0" & UNICHAR ( 160 ) & ";(#,##0);-"
& UNICHAR( 160 )
)When we looked at custom number formats above, we saw they were applied to the measure. The FORMAT function is different, it is included within the measure (as shown by the screenshot below [1]).
There are three IMPORTANT things to be aware of here:
- Since Power BI ignores spaces used within the FORMAT function, we have applied the non-breaking space character with the UNICODE(160) function.
- FORMAT returns text. Therefore, it is likely the values will be left-aligned by default. Therefore, we need to right-align the column to make it look like numbers.
- The format codes we saw in the section above can be adapted to work with the FORMAT function too.
To right-align a column in Power BI
- In the Visualization pane, click Format [1] and ensure Visual is selected [2]
- In the Specific Column section, select the Series and apply the right alignment settings [3].
For more info about using the FORMAT function check this out: https://docs.microsoft.com/en-us/dax/format-function-dax
Comparing methods to show negative numbers in brackets
The screenshot below is a comparison of all 3 methods. Which one should you use?
The decision is not about the best method to show negative numbers in brackets. Instead, it’s about understanding your options for each circumstance.
The things to consider are:
- Do you want a leading currency symbol?
- Do you care if digits are correctly right-aligned?
- Should the number be rounded to the nearest thousand, million, etc?
- Are you showing different number formats inside a single column?
By answering these questions, it should lead you to decide which is the best option for your scenario.
Hopefully this post has set you on the right path to show negative numbers in brackets in Power BI.
What to know more tricks with Matrix visuals? Check out this post: Set equal column widths in a Matrix visual