You can divide in Power BI using a number of different techniques. While the fastest is to use the DIVIDE DAX function, it may not always be the most straight forward or appropriate. We’ll explore the different options Power BI provides for division and explain when to use each one.
Division is a vital part of Power BI. It’s commonly used to calculate ratios, normalize data for scale, and create custom KPI’s that report viewers are interested in seeing.
Let’s look at the standard way to divide in Power BI, the DIVIDE DAX formula, and how to perform division in Power Query.
We’ll also explain when to use which one and the pros and cons of the different options!
Table of Contents
How to Divide Two Values in Power BI
There are three methods to divide two values in Power BI. Power BI developers can either sum two columns using a DAX SUM Formula and divide them using the / divided by sign. Alternatively, they can use the Power BI DAX formula DIVIDE to automatically perform the calculation and automatically handle zero denominators or perform the operation in Power Query by creating a new column.
As a quick reminder, division is made up of two components.
The Numerator or number being divided, and the Denominator, the number which the Numerator is divided by.
We’ll reference these as we explain the different ways to divide numbers in Power BI.
In the following example, we will calculate the % Cost of Goods Sold for a fictional hotdog stand.
Price is the Numerator, and Cost is the Denominator.
The image below is the dataset that we will be working with.
To divide in Power BI, folow these steps.
Step 1.) Create a New Measure
Step 2.) Enter in a DAX formula to SUM each of the values that you are dividing.
The formula will look like this:
New Column = SUM('TableName'[Numerator]) / SUM ('TableName' [Denominiator])You’ll notice that both the Numerator and the Denominator have a formula to SUM them prior to dividing. This is because DAX is designed to allow report viewers to analyze their own data, and drill down into details.
Using the same data from our starting example but removing the category will aggregate the results to show us what the total % cost is for all items sold.
Because both columns used in the divide by formula already have SUM Applied, an end user can view it at a total level, or they can view it at a detail level without adjusting a formula. Power BI will automatically scale the calculation based on what the report viewer is requesting.
What is the Division Symbol in DAX?
Power BI uses the / symbol to notate two values that are being divided in DAX formulas. Alternatively, Power BI developers can use the DIVIDE DAX Formula. Both methods will return a ratio of divided numbers, and we’ll look at when to use each one.
How to Avoid Errors When Dividing by Zero in Power BI
If you recall from math class, dividing by zero is not possible, though attempts have been made in higher math theory. In Power BI, dividing by Zero will result in an error. This is very similar to Excel, and the way that it is handle in DAX is also similar. Developers can use the DAX formula IFERROR.
The formula will look like this:
New Column = IFERROR(SUM('TableName'[Numerator]) / SUM ('TableName' [Denominiator]),ValueIfError)The syntax adds on to our previous formula, and starts it with an IFERROR DAX formula.
After the division formula there is a comma and a Value. The value could be any number you want to return when an error occurs. Most common this could be the number 0, or you could use two quotes “” to return a blank.
The IFERROR DAX function can be used for additional use cases and will return whatever value you want at the end of it.
What is the Divide Function in Power BI?
Our preferred option for dividing numbers in Power BI DAX is the use of the DIVIDE formula. It has several advantages over using the normal / method.
- DIVIDE is easier to use.
- DIVIDE is easier to read
- DIVIDE supports automatic error handling.
- DIVIDE is faster.
The formula will look like this:
New Column = DIVIDE([Numerator],[Denominiator],ValueIfError)By default, DIVIDE will sum the numerator and denominator, then divide them. The third criteria is the to return if there is an error in the calculation.
When to Use DIVIDE instead of / in Power BI to Divide Numbers
The DIVIDE DAX formula is a preferred option because it is quick, easy to read, and has automatic error handling. However, it should be avoided when you need to have more granular control over calculations. DIVIDE will only aggregate values using a SUM. Any other aggregation methods, such as Average or First would need to be written out as part of a longer formula.
Because of its simplicity DIVIDE may be difficult to incorporate into a longer DAX formula where the order of operations becomes important, such as nesting formulas to avoid creating multiple DAX measures for a single calculation.
Using Quick Measures to Divide in Power BI
Power BI developers can use Quick Measures to create a number of common calculations without writing any formulas or code. To create a quick measure, click on Quick Measure from the Table Tools tab of the Power BI Ribbon. Select “Division” from the Quick Measure drop down and drag and drop the field names from the Data Panel for the Numerator and Denominator.
Click on Quick Measure from the Table Tools tab of the Power BI ribbon will launch the Quick Measure builder.
Power BI supports a number of additional calculations such as percentage difference, multiplication, subtraction and division if you prefer a zero code experience.
Suggestions with Copilot will also convert plain text requests to Power BI DAX formulas for you. You can describe the calculation that you want and Power BI will use advanced AI to generate formulas that you can use in yoru reports.
How to Divide Numbers Using Power Query
To divide two columns in Power Query, you have to create a Custom Column and type a formula. Launch the Power Query Editor and follow these steps.
Step 1.) Click on Add Column > Custom Column
Step 2.) Name the New Column
Step 3.) Use the formula [Numerator] / [Denominator]
Both column names should be surrounded in square brackets. Once you click okay, Power BI will create a new column dividing the two defined columns in your dataset.
The Custom Column formula will look like this:
Note: While there is a button for standard calculations under the Transform section, it will only allow you to divide a column by a specific number.
Dividing two numbers in Power Query is a straightforward process that can be accomplished in various ways. Below are some methods you can use:
Note: While less common than using DAX it could be appropriate to perform division and other calculations in Power Query when working with large datasets. Power BI will perform Power Query calculations when refreshing the dataset instead of performing them when a user refreshes a visual.
Conclusion
Power BI provides users with several methods to divide two columns. The preferred method is to use the DIVIDE DAX function. It’s quick, simple, easy to read, and has built in error handling. The primary drawback is that you lose the ability to control aggregation methods and it may be more difficult to embed into longer calculations.
Two columns can be divided using Power Query to create a custom column. Define the column names with square brackets surrounding them and use the / symbol in Power BI to define the numeric operator for division.