Power BI Accounting Format of Dollars

Level: Beginners

As I often say, sometimes it is the little things that can make a big different for some people – in this case Accountants.

I was teaching a Power BI class a few weeks ago and I was discussing how the number formatting options work in Power BI.  One of my students (Sheldon) asked a question “How come my currency format has brackets for negative numbers e.g. ($200) ?  I have been using Power BI for a long time and I was pretty sure he made a mistake – I “knew” this couldn’t be done.  So I went over to Sheldon to check it out and to my amazement, there it was!  Bracket formatting around the $ currency to indicate negative numbers – see the matrix below.

image

As it turns out, this option is available when you select $ English (United States) as the currency format.  If you are in the United States, this comes up automatically if you select Currency General too (apparently).

image

Because I live in Australia I have always selected “Currency General” and this format (for Australia) does not include the brackets for negative numbers.  It is interesting that English United Kingdom includes a pound symbol and does not have the brackets either (at least not when I do it here in Aus).

Why is this Important?

Brackets around numbers to indicate negatives is a very important visualisation for accountants, and I have been asked many times if this feature exists.  In fact I think accountants prefer to omit the $ symbol all together to get a cleaner view of the numbers.  But the above deployment is better than nothing else for now and I am sure Microsoft is working on a broader range of improved number formatting options.

Red Colour for Negatives

Now that I know that I can use brackets for negatives, I remembered an old comment from  Amanda Cofsky about how to format the numbers as red for negatives and black for positives – this feature was released in Aug 2017.  In short you do it as follows:

  1. Select the Measure
  2. Select Conditional formatting
  3. Select Font color scales
  4. Set the minimum to “number” and 0
  5. Set the maximum to “number” and 0

image

image

After applying the above formatting, you get the following visualisation.

image

Note the test measure I created (and added to the matrix above) is simply “Test = 0”.  I have formatted this test measure with the same colour formatting as above to prove that a zero will be formatted correctly in black.

Wrap Up

OK, so maybe it was only me that didn’t know this.  But possibly others living outside of the USA that didn’t know this either given that currency general only seems to apply brackets if you have your PC set up for the USA.  For those that didn’t know, now you do.

Share?

Comments

  1. Useful tip Matt!

    You’re right that little things can make a big difference. I am in Canada and have had the same issue with formatting. I sometimes set up Windows in English US to avoid this and other “nuances.”

    Thanks for sharing!

  2. As an accountant type I can vouch for wanting brackets to represent negative values. Weird when I don’t see them. And I for sure don’t like $ symbol all over the place. Highly annoying. If you need an indicator that money is what the pivot is about, you might be in the wrong profession.

  3. By using Format you can have many custom number formatting just like in excel though still struggling to add UOM etc in number formatting.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x