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 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. You will not be returned an error. Instead you will be returned with the symbol for infinite ∞. It is also common for people not to spot this problem. This is because when you are working with data in Power BI or Power Pivot, only a subset of data is visible. It is very possible that zeros are further down your data set and you have not seen them.The DIVIDE Solution
So how can you overcome this problem in Power BI or Power Pivot? DAX is to the rescue with the DIVIDE function. The syntax is simple =DIVIDE(Numerator, Denominator, [AlternateResult])
Let’s have a look at both the DIVIDE function and the divide operator in action. We have a table of data containing columns for Date, Invoice number, Product, Units sold, Total sales and total cost price. What we want to do is calculate the Gross profit %.
First we will set up a new calculated column. This column will contain the expression
= [Total Sales Price] – [Total Cost Price]
This will return the gross profit for each row in the table. We can rename this column Profit.
To get the Gross Profit % we now need to divide the Profit by the Sales. Let’s do this two ways to see the difference. First we will look at the m...