Power BI number formatting tips

Here are two tips that I use repeatedly when building reports in Power BI. “Repeatedly” is a bit of a stretch as I’m still a newbie with Power BI but this was remarkably hard to find so I’m blogging for my future self, when I forget. :)

For Tip #1, I love being able to format numbers in Excel easily with the click of a mouse button, but in Power BI, it’s not quite as straight forward. I like replacing 0’s with dashes, and I explain one way for you to do that.

For Tip #2, there are times where there is no data and the values show as blanks instead of showing a zero. This can occur on Card visuals where it shows “(blank)” and it can occur on matrix or other kinds of visuals too where “nothing” is just an empty space. If it’s a numeric or currency visual, I want to display the dash because that empty space is a zero value.

Tip #1 - Using Excel formatting syntax to format numbers

In Power BI Desktop, I’m doing this in “table view” but theoretically wherever the Column Tools menu appears this will also work. Here is an example of what my data looks like before the formatting is applied.

Before picture

The highlighted values are what I want to show as dashes.

Highlighted sections are what I want to update

How To

Start by going to Table view in Power BI desktop and find a table/column you want to adjust. It can be a measure that you know is a number/dollar or it can be an actual visible column.

1. Click on the field you want to format on the right hand side fields listing.
2. If the Column Tools menu isn’t selected by default, click on that menu.
3. Click in the Format drop down list. It’s editable!
4. Type or copy/paste from the formats below and click Enter/Return on the keyboard to set the format.

The screenshot below shows as best as I could capture, where and how to do this.

Table view, with a number format pasted in the Format box.

• “Estimated Dollars” is a currency field where I want 0 decimal places, a dollar sign, dashes for zeros (and brackets around negatives although my data doesn’t show an example of that).
• “Estimated Hours” is a number field, 2 decimal places, no dollar sign, and dashes for zeros (and also brackets around negatives).

After picture

Here is what the result looks like afterwards:

Highlighted sections are what are now displaying as I want to see them.

Format Strings to copy

The formatting strings are (or should be) the same as what you might find in Excel, except the inclusion of colour notations aren’t supported. (I.E., putting [Red] in the format string will show [Red] instead of colouring the values red.)

The 3 segments in each formatting example represent how to display a positive value, a negative value, and a zero value. Note I have a space at the end of segment 1 (before the first semi-colon) so that the numbers align visually when there may be negatives (the bracket fills the space on negative values). Same for the dash, so that the dash more-or-less aligns with the numbers. I’ve put a space after the $ sign but that too is editable if you don’t want that look.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

Currency example: no decimals, $ sign, negatives in brackets, "-" for zeros

$ #,##0 ; ($  #,##0);"- "

Currency example with decimals (otherwise same as above)

$ #,##0.00 ; ($  #,##0.00);"- "

Number example without currency symbol (2 decimals in this case)

#,##0.00 ; (#,##0.00);"- "

I have not tried any further formatting as 90% of the time, this is what I need and since I forget the syntax, I will be copying and pasting from my own blog for years to come. :)

Tip #2 - How to display 0 instead of (blank)

This is the point of the post I feel like a cheesy infomercial dude. “But wait! There’s more!”. :)

Before picture

In the last screenshot above from Tip #1, I took that same screenshot after replacing zeros with dashes, and highlighted different fields where columns are displaying blanks. I want those displayed as dashes too since the value in them is 0.

“Before” with blank values

How To

This is what needs to be done, on your measure, to fix this. The keyword there is it’s on the measure so if your data is not there, there is no way to make it display a zero but you can with a measure.

The fix seems absurdly simple. Add “+0” to the end of your measure. Effectively what that’s doing is turning the result into a number even if there is no value present in that measure’s result.

After picture

Here is the same screenshot afterwards. Two of my columns were using measures. I only had to apply this to those 2 measures, any other column which is using a mathematical formula then picks up the zero in its result. Note, however, one red highlighted cell which remains blank. That is a column which I did not use a measure on. In order for me to resolve that, I need to make a measure for the values in that column.

The results are showing as dashes because I have formatted the columns using Tip #1 to display zeros as dashes.

“After” shot showing the previous blanks now are zero values.

Summary

There may be multiple ways to resolve this same issue. I’ve seen other posts where the formatting is done within the measure itself for Tip #1, but this seems far simpler overall. I have not testing this further so I don’t know where else you could apply the scenario but in theory, if you do not like the date format options, for example, you could change them with similar syntax for date formats.