Nobody likes seeing errors in their reports and one of the most common causes for errors, or the use of elaborate work arounds that I see is from people not using the Divide Function in Power BI.
If you're coming from an Excel world, with experience in creating Excel based reports then you will know how frustrating it is to an error because something is dividing by zero. If you know of this problem then you most likely know how to fix it through either an 'If Error' function or maybe even conditional formatting for errors.
But in Power BI these solutions aren't available in the same way and I have seen people building reports with a simple divide statement. For example, if you want to work out a percentage change versus last year, you may have something like this:
SALES REVENUE % CHG VS YA =
'Sales Data'[SALES REVENUE CHG VS YA] / 'Sales Data'[Sales Revenue YA]Whilst this isn't an incorrect formula and will give the right result, it isn't best practice and will result in errors appearing in your reports where the division finds that last years sales are 0.
With an Excel hat on you may start looking for fixes for this problem as you may have previously, which may result in some complex DAX. But in Power BI the Divide Function was designed to handle cases where a division is by zero.
Using the Divide Function in DAX changes our year on year percentage change function to be as follows:
SALES REVENUE % CHG VS YA =
DIVIDE('Sales Data'[SALES REVENUE CHG VS YA],
'Sales Data'[Sales Revenue YA],
0)In this case the numerator and denominator haven't changed, but are now separated by a comma, whilst the final '0' is the 'Alternate Result' which is provided in the case of a division by zero. You can change the alternate result to be anything you need it to be, whilst you can also leave it as blank, although that will return a blank.
Easy to implement, and straight out of the box functionality that can save you a lot of time and effort when trying to plan for a divide by zero case.
