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, but in Power BI, it's not quite as straightforward. I like replacing 0's with dashes, and I explain one way to do that.

For Tip #2, there are times when 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 a matrix or other kinds of visuals where "nothing" is just a space. If it's a numeric or currency visual, I want to display the dash because that 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.

"Before" shot showing zeros display as zero.
A “before” screenshot shows 0.00 for zero values.

How To

Start by going to Table View in Power BI desktop and find a table/column to adjust. It can be a measure or it can be an actual visible column.

  1. Click on the field 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 in Power BI showing the Formatting drop down list where a format has been pasted in.
Screenshot of Column Tools in table view in Power BI with Format highlighted.
  • "Estimated Dollars" is a currency field where I want 0 decimal places, a dollar sign, and dashes for zeros (and brackets around negatives although my data doesn't show an example of that).
  • "Estimated Hours" is a number field, with 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:

"After" picture showing zeros are now formatted as dashes
The “after” screenshot shows dashes instead of “0.00”.

Format Strings to copy

The formatting strings are similar to what one might find in Excel, except the inclusion of colour notations isn'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.

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 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" shot showing column values of zero are blank.
The “before” screenshot with some blanks that I want to replace.

How To

This is what needs to be done, on a measure, to fix this. The key phrase there is "this needs to be done on a measure" because if the data is not there, there is no way to display a zero but a measure can display something else.

The fix seems absurdly simple. Add "+0" to the end of the 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.

Measure formula with a +0 at the end
Example of a measure with +0 after the calculation.

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, that one red highlighted cell remains blank. That is a column on which I did not use a measure. 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" show showing the blanks now have a value
The “after” screenshot showing most of the blanks are now showing dashes instead of blanks.

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.