Power BI: How to bracket negative numbers in a financial statement

Power BI
iStock_AW_ MATJAZ SLANIC
Share this content

If you use Power BI to produce your financial reports, you may well want to display negative numbers in brackets. This is not a pre-set option in Power BI, so how exactly can it be done? 

When you're building your financial repor tin Power BI it'll display your numbers as follows:

So how can you make it look like this?

If you are happy to display the $ sign in front of your numbers, you can get around the problem using your computer settings. However, I am sure that for most of you based in the UK this is not an acceptable solution. Fortunately, Power BI has a DAX function called FORMAT that enables you to customise your report.

The FORMAT Function

As the name suggests, this function enables you to format the way numbers are shown in the visuals. The general syntax of this function is: FORMAT ( <value>; <format_string> ).

The value is either an expression or another measure. It is usually better to separate the expression from the FORMAT function (making the expression a full measure) to be sure to be able to use the value everywhere, since the FORMAT function changes the data type from numeric to text.

The format_string field defines the format itself. Power BI accepts some predefined formats. They are described in the Microsoft DAX Reference. The four ones you may use the most are:

Format String

Definition

0 becomes…

1563,248 becomes…

“Currency”

Displays the thousand separators, two digits to the right of the decimal separator and your currency formatting.

£0.00

£1,563.25

“Fixed”

Displays at least one digit to the left of the decimal separator and two to the right.

0.00

1536.25

“Standard”

Is identical to “Fixed” but displays thousand separators as well.

0.00

1,563.25

“Percent”

Displays the number as a percentage, two digits to the right of the decimal separator and the percent sign (%).

0.00%

156324.80%

Here is a concrete example of the syntax:

Now, unless you are working with US dollars, none of the predefined formats enables you to put your figure in brackets. Fortunately, you can create your own formats.

Use custom formatting to bracket your negative numbers

There are many options for your FORMAT function. We will not go through all of them in this article as they are described in the DAX reference source. However, you may understand the most important ones having a look to the following example:

Format_Actual = FORMAT ( [PLAmtToLastMonthLastYear]; “##,###;(##,###);-“ )

What this measure does is calculate the P&L amounts from the related measure and then apply the defined format to the value. This format can be composed of up to three arguments separated by semicolons:

  • The first one sets the format of positive numbers;

  • The second one sets the format of negative numbers;

  • A third argument may be used to set the format of null values.

Only one argument is necessary if there is no need to specify the format for negative and null values.

Coming back to the previous example, and the hashes representing digits, we have:

Positive value

Negative value

Null value

1,563

 

##,###

(1,563)

 

(##,###)

-

 

-

A positive value is displayed with the thousand separators and no digit to the right of the decimal separator.

A negative value is displayed the same way but with brackets.

A zero is displayed as a dash.

 

 
Here are some tips you may need and on which you may be able to base your own customisation:

Format String

Definition

3256.10… becomes

-1563.248…

 

0…

“##,###;

(##,###);-”

Displays the thousand separators, no digit to the right of the decimal separator, brackets if the figure is negative and a dash if 0.

3,256

(1,563)

 

-

“£##,###;

(£##,###);£ -”

Is identical to the previous one but displays the chosen currency formatting as well.

£3,256

(£1,563)

 

£ -

“##,###.00;

(##,###.00)”

Is identical to the first format but displays two digits to the right of the decimal separator. Zeros are processed as positive numbers.

3,256.10

(1,563.25)

 

0.00

“#.##”

Displays all the numbers (positive as negative) without any thousand separators but two digits to the right of the decimal separator. Leading and trailing zeros are not displayed.

3256.1

-1563.25

 

.

“0%;(0%)”

Displays the number as a percentage with the percent sign and brackets for negative numbers, no digits to the right of the decimal separator.

325600%

(156325%)

 

0%

“##,###”

Displays the thousand separators and no digit to the right of the decimal separator.

3,256

-1,563

 

0

FORMAT is not without its problems

As pointed out earlier, the use of the FORMAT function turns numeric values into text values. It is important to be aware of the implications of such a change when you bracket your negative values. Prudence is required.

Discrete values in a visualization

In a card, chart or matrix, the format applied in a measure definitely displays the values formatted, but the fact that they now are text has consequences.

Firstly, it is interesting to notice that the total amounts are still correctly calculated. This is explained by the fact that the values are calculated on the fly; the total is not the sum of all the other amounts, but the measure applied without any row filtering.  So the value is calculated and then the FORMAT applied.

Secondly, note that the alignment is changed.

This is not what we want.  Our P&L report should show the values right-justified.

You can resolve this easily in the field formatting settings of the matrix or chart – not forgetting to apply the same alignment to headers and totals.


 

Null values not picking up the “-” format for zero

Now, you may notice blanks in your chart even if you defined your zero format to show dashes:

The reason is that there is actually no value, not even a zero. Our FORMAT function is looking for a zero as one of the conditions.

If you want to make sure that you have “-” in place of blanks, you can get around it with an IF condition:

Filtering

As the values now displayed are text values, the filtering settings you can apply on you visual are text filters options; which prevents you from selecting a range for instance.  You can overcome this by using the original unformatted numeric measure as your filter.

Displaying blank rows in a table or matrix

A consequence of replacing blanks with “-” is that they are no longer blank.  They are now a text value of “-”. If you are only displaying one column you can resolve this easily with a filter for “Is Blank” on your original numeric measure.

If you are displaying more than one column though, you need to be more careful.  You only want to exclude a row when all columns are blank. See the “7106 – Rent Received” row below:

A P&L statement that previously fitted neatly onto one page may no longer do so.  A potential solution in this case is to use a visual-level filter to remove these.  In this case, we can simply apply a visual-level filter to exclude cases where our unformatted YTD measure is blank since there will be no case where the Month is not blank and the YTD is.  You can see how to do that in this video.  If you cannot guarantee that Month is blank if YTD is not, then you could create a new measure for the filter.  Something like this:

Then we can use this [PLRowAllZero] measure as a filter to exclude rows where all values are zero.

Visualizations using continuous values

Visualizations like line charts that require a continuous value cannot work with the new formatted version of your measures, since these are now discrete, or categorical, values.  For these visualizations, you will need to revert to your original (unformatted) measures.

Use in other expressions

Finally, and more surprisingly, you can include a measure that has such a format as a variable in other measures. I do not recommend this though.  Firstly, the way Power BI works in this case is that it will re-convert the text-formatted values back to numbers and then use the number in an expression that in turn will produce a number as a result.  This is not efficient, and you would be better off using the original “un-formatted” measures. Secondly, this won’t work if you have used the “-“ trick to show blank values since Power BI does not know how to convert “-“ to a number.

Summary

FORMAT is a useful function to set the format of your values. It can be used with predefined formats but also with customised ones. This way, you can brackets your negative numbers using the second argument of the custom format string; you can also display zero values as a hash as well since it is another financial convention.

However, the FORMAT function converts numeric values into text and this has a number of undesirable consequences.  My advice is to maintain the original numeric measure and create a new measure that handles this formatting. Clearly labelling the measures that are just used for formatting makes it easy to distinguish them from your regular numeric measures.

Useful links

About Marion Letertre

Marion

Marion Letertre works for Accounting Insights, which provides Power BI training and reports for accountants and SMEs.

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.