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.
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).
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:
- Select the Measure
- Select Conditional formatting
- Select Font color scales
- Set the minimum to “number” and 0
- Set the maximum to “number” and 0
After applying the above formatting, you get the following visualisation.
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.
Note: It is still not possible to format negative numbers with brackets WITHOUT using the currency symbol. If you want this, you will have to vote for one of the ideas at http://ideas.powerbi.com
By using Format you can have many custom number formatting just like in excel though still struggling to add UOM etc in number formatting.
Yes you can use format, but it turns the numbers into text, so they can’t be displayed on a chart there after.
I wonder how much eventual overlap with Excel functionality will exist as time passes….perhaps 85%?
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.
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!