
Power BI Pro Tip: show 0 instead of (Blank)
This one is easy once you know how to do it. It is also buried in the documentation. Kasper de Jonge mentioned it to me on Twitter and I thought it would not hurt to just write it down quickly (also to make it easier for myself to find it in the future):
Default behavior of Power BI is to show (Blank) when there is nothing, even when summing a numerical column that happens to be all empty. But what if you want to show 0 instead? Well, with COALESCE you can.
In this simple example I have a table showing sales per region for a company that is not doing so good – they are not selling anything.

Assuming SalesAmount is a numerical column, if you make a visual with this you get:




Business users might just want to see 0 in this case, not the (more correct) (Blank). To make this happen, add a simple measure:
TotalSales = COALESCE(SUM('Sales'[SalesAmount]),0)
Creating a visual with that measure shows:




The way this works is that the COALESCE function will just return the first thing it finds reading from left to write in the parameter list that is not null/blank. That happens to be 0. If for whatever reason you wanted to show -1 when there are no sales, you can do that as well, just change the 0 to -1.
Enjoy and don’t forget to feel good about making your business users happy 🙂
Share this:
- Click to share on LinkedIn (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on Skype (Opens in new window)
- Click to share on WhatsApp (Opens in new window)
- Click to share on Pocket (Opens in new window)
- Click to share on Tumblr (Opens in new window)
- Click to share on Pinterest (Opens in new window)
- Click to share on Telegram (Opens in new window)
- Click to share on Reddit (Opens in new window)
- Click to print (Opens in new window)
- Click to email this to a friend (Opens in new window)
Related
You May Also Like

Stepping back into the ring.
July 24, 2019
Azure SQL firewall settings for Power BI refresh
April 28, 2020