The Divide Problem
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 mathematical operator divide and then we will look at the DAX function DIVIDE().
In a new calculated column we will enter the expression
= [Profit] / [Total Sales Price]
If you look down the table you will find two invoices where the sales price is zero. And look what’s happened with the calculated column for these rows. The value returned is not error but instead ∞.
To overcome this we can use DAX DIVIDE function.
Let’s now add a new calculated column and this time our expression will be
=DIVIDE([Profit], [Total Sales Price])
In this example we have not added the [AlternateResult] as this is optional. By default the alternate result will be blank.
If we now edit this expression to read
=DIVIDE([Profit], [Total Sales Price],0)
This will now replace the blank with 0.
So my advice, well it’s better to be safe than sorry right. Always use the DAX DIVIDE function when you need to divide values.
Watch an example by video:
If you are interested in learning more about Excel, Power BI and Data analysis and modeling with DAX then follow me.
Want to know more about Power BI and Excel Power Tools, have a read of this post from yesterday
/@paulag/analyzing-steemit-data-using-power-bi