Power Pivot Principles: Nothing to Draw a BLANK About – ISBLANK Zero?

Welcome back to the Power Pivot Principles blog. This week, we take a look at a common mistake made with zero(e)s.

I am going to continue where I left off last time. Last week, we looked at the following dataset:

We created two measures,

These two calculations created the following PivotTable:

I am going to create another measure to ascertain the month on month percentage growth in Sales:

Please note that I am deliberately staying away from the DIVIDE function this week as I wish to make a point about zero(e)s, the BLANK function and the ISBLANK function.

Upon creating this measure, I get the following result:

Oh dear.  We get some #NUM! errors.  That’s not good.  These are caused for two reasons:

1. The Jan 2020 result returns #NUM! because Total Sales 1 Month Ago literally – as well as figuratively – draws a blank.  It’s not returning any result, so it’s not just zero that may cause a division error in DAX code.
2. The Feb 2020 result returns #NUM! for the usual mathematical reasons.  Total Sales 1 Month Ago returns zero (0) this time – and dividing anything by zero is mathematically indeterminate, hence the #NUM! error.

It’s never good having prima facie errors in our outputs, but this measure may be easily remedied:

Having wrapped the measure in an IF statement, I get the following result:

This still isn’t really correct, as we haven’t achieved zero growth for the first two months – we have just created two calculations that cannot be computed. The BLANK function may be a better option:

This looks better:

In fact, if we remove the fields Total Sales and Total Sales 1 Month Ago from the PivotTable, there is something else to note:

Do you see how the first two months disappear? This is because when all displayed measures return BLANK(), relevant headings will be suppressed automatically. This can prove quite a useful trick in financial reporting.

However, if you do want them to show, there is an option. If you right-click inside the PivotTable,

select ‘PivotTable Options…’ and then

go to the Display tab and check ‘Show items with no data on rows’ and / or ‘Show items with no data on columns’ as appropriate.

You can also consider ISBLANK, which pretty much does the same thing it does in Excel.

This returns a different result:

ISBLANK can distinguish between zero(e)s and blanks.  That’s not what we want here as this brings our #NUM! error back for Feb 2020.  The previous formula is more appropriate, since we did have zero sales entered in Jan 2020.  However, sometimes you want to distinguish between a value of zero and a blank value; ISBLANK is perfect for that.

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.