Dutch Data DudePower BI Pro Tip: show 0 instead of (Blank)
12 May 2020This 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 :)