Power Pivot Principles: Drawing a BLANK on Error Traps

Welcome back to our Power Pivot blog series.  Today we discuss how to include error traps in our measure formulae.

This blog continues from our previous blog on Measure Creation Practice.  Another common measure that we might want to create is a ‘Profit Margin’ measure:

Nothing seems to be wrong with this formula, even Power Pivot thinks so.  But what if there were no sales in one period?  We would get a division by zero error.  To avoid the potential error, we can use the IF function to provide us with an error trap:

This will return a zero value when the sales are nil. But why report it anyway? We can use the BLANK function to further streamline this measure viz.

Now the value will be suppressed in the Pivot Table if sales are blank, leading to clearer, more concise reports.

That’s it for this week, stay tuned to our blog page for more on Power Pivot. 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.