DAX Divide Function – DAX for Power BI and Power Pivot
When you are working in Excel it is relatively easy to divide two numbers. You take the value from one cell, and divide it by the value or the in another cell. For Example =B3/B2. Divide is a basic mathematical operator. In Excel if you try to divide a value by zero you will get the error #DIV/0!
Power BI and Power Pivot don’t work on cell references like Excel. They both work on columns of data. It is very common to add a calculated column using divide. A perfect example would be on a sales table to take the profit column and divide it by sales column to get the gross profit %.
Using the mathematical divide in Power BI or Power Pivot can lead to a problem if your table of data contains a 0. Instead of returning an error, you will be returned with the symbol for infinite ∞. It is also common not to spot this error. This is because when you are working with your data in Power BI or Power Pivot, only a subset of your data is visible. It is possible that zeros are further down your data set and you have not seen them.
So how can you overcome this problem in Power BI or Power Pivot? DAX is to the rescue the DIVIDE function. The syntax is =DIVIDE(Numerator, Denominator, [AlternateResult])
In this video ‘DAX for Power BI and Power Pivot – DAX Divide Function’ you will see how using the standard mathematical divide can yield ∞. You will also see how this can be avoided by using the DAX Divide function