How to Divide in Power BI

Performing a division calculation seems straightforward, but in Power BI, it's one of the first places new users run into trouble. This guide will walk you through the best way to handle division in Power BI using DAX, ensuring your reports are accurate, error-free, and easy to build.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Simple Division Can Be Tricky in Power BI

In a tool like Excel, you might just use the forward slash (/) to divide one cell by another. While you can do that in Power BI, you'll quickly discover a common issue: the infamous "divide by zero" error. This happens when the number you're trying to divide by (the denominator) is zero, which is mathematically impossible. In your data, this could be a day with zero ad clicks, a product with zero sales, or a website with zero visitors.

When Power BI tries to divide by zero, it can return results like Infinity or NaN (Not a Number), which can break your visuals or produce confusing charts. The best practice is to handle this possibility proactively, and thankfully, DAX (Data Analysis Expressions), Power BI's formula language, has a function specifically for this.

The Best Method: Using the DIVIDE Function

The safest and cleanest way to perform division in DAX is with the DIVIDE function. It's designed to handle the "divide by zero" scenario automatically, saving you from writing more complex error-handling formulas.

Understanding the DIVIDE Syntax

The function is structured like this:

DIVIDE(<numerator>, <denominator>, <alternate_result>)

Let's break that down:

• <<numerator>>,: The number to be divided (the number on top).
• <<denominator>>,: The number you are dividing by (the number on the bottom).
• <<alternate_result>>, (Optional): The value you want to return if the denominator is zero. If you leave this blank, the function will return BLANK(), which is often exactly what you want.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example 1: Calculating Cost Per Sale as a Measure

Let's say you have a sales table with columns for Revenue and Units Sold. You want to create a measure to calculate the average price per unit, which is "Total Revenue" divided by "Total Units Sold."

Measures are dynamic calculations that respond to the context of your report (like filters and slicers), making them perfect for this kind of Key Performance Indicator (KPI).

Step-by-Step Instructions:

1. In the Power BI report view, find the table you want to add the measure to in the Data pane on the right.
2. Right-click on the table name (e.g., SalesData) and select New measure.
3. The formula bar will appear at the top. This is where you'll write your DAX formula.
4. Enter the following DAX expression:

Average Price Per Unit = DIVIDE(SUM(SalesData[Revenue]), SUM(SalesData[Units Sold]))

What's happening in this formula?

• SUM(SalesData[Revenue]) acts as our numerator, aggregating all the revenue in the current context.
• SUM(SalesData[Units Sold]) is our denominator, aggregating all the units sold.
• We didn't provide an alternate_result, so if SUM(SalesData[Units Sold]) is zero for a given product or time period, the measure will return BLANK(). This is great because Power BI visuals, like a line chart, will simply skip plotting a point for that period instead of incorrectly dropping to zero.

Once you press Enter, your new measure will appear in the Data pane with a small calculator icon next to it. You can now drag this Average Price Per Unit measure into a table, card, or chart visual to see your result.

Example 2: Handling Blanks with an Alternate Result

Sometimes, seeing a blank space isn't ideal. You might prefer to show a zero. Let's calculate a "Conversion Rate" and tell the DIVIDE function to return 0 when there's no traffic.

Imagine a web analytics table named WebsiteTraffic with columns for Conversions and Sessions.

Step-by-Step Instructions:

1. Create another new measure by right-clicking your table and selecting New measure.
2. Enter this formula:

Conversion Rate = DIVIDE(SUM(WebsiteTraffic[Conversions]), SUM(WebsiteTraffic[Sessions]), 0)

Here, we've added 0 as the optional alternate_result. Now, if a campaign or a specific day had zero sessions, your 'Conversion Rate' visual will show a 0% instead of a blank space. This can make tables look cleaner, but be mindful when using it in charts, as it can create misleading drops to the baseline.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Alternative: Using the Forward Slash (/) Operator

You can also use the standard forward slash (/) for division, but it lacks the built-in safety of the DIVIDE function. It's generally not recommended for measures where the denominator could potentially be zero.

Using our first example, the formula would look like this:

Average Price (Slash) = SUM(SalesData[Revenue]) / SUM(SalesData[Units Sold])

If you have any product in your report context with zero units sold, this measure will return Infinity. To prevent this, you'd have to wrap your formula in an IF statement to check for zero first:

Average Price (Slash) = 
IF(
    SUM(SalesData[Units Sold]) = 0,
    BLANK(),
    SUM(SalesData[Revenue]) / SUM(SalesData[Units Sold])
)

As you can see, this is far more complicated and harder to read. The DIVIDE function does all of this for you in a much cleaner, single-line formula. For measures, always prefer the DIVIDE function.

When to Divide in a Calculated Column vs. a Measure

So far, we've focused on creating measures. But you can also perform calculations in a calculated column. The key difference lies in when and how the calculation is performed.

Calculated Columns

A calculated column computes a value for each individual row in your table and stores that result back into the table itself. This calculation happens during the data refresh process. It uses memory (RAM) because the results are stored in your data model.

You should use a calculated column when the value is static and specific to that one row.

Calculated Column Example:

Let's say your SalesData table has Order Revenue and Item Quantity on each row for an individual transaction. You could create a calculated column Price Per Item that performs the division row-by-row:

1. Go to the Data view in Power BI (the grid icon on the left).
2. Select the table you want to add the column to.
3. From the Table tools ribbon, click New column.
4. Use this formula:

Price Per Item = SalesData[Order Revenue] / SalesData[Item Quantity]

Notice we are not using SUM() here because calculated columns operate on a single row at a time. The result is calculated for each row and saved in your model. You could then use this new column as a category or axis in a visual, for example, to see how many items were sold in different price brackets.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Measures

A measure, on the other hand, is not stored in your model. It is calculated on-the-fly based on the filters applied to your report (e.g., date slicers, campaign filters, user selections). It calculates an aggregation of many rows.

Most of your business calculations, like conversion rates, average order values, and cost-per-click, should be created as measures. They are more flexible and performance-friendly for aggregations.

Summary: column vs. measure

• Use a calculated column for a static, row-level expression that you need to use as a slicer, filter, or category.
• Use a measure for pretty much all aggregated ratios and KPIs. When in doubt, a measure is probably the right choice for division.

Final Thoughts

Mastering division is a fundamental step in building powerful Power BI reports. By using the DAX DIVIDE function for your measures, you'll create cleaner formulas and automatically protect your visuals from "divide by zero" errors. Remember to use measures for dynamic aggregations and opt for calculated columns only when you need static row-level results.

Figuring out DAX quirks like division is just one of many small steps involved in building dashboards. Creating reports still often feels like a full-time job of pulling data and learning specific formulas. This is exactly why we built Graphed. Instead of writing formulas, you can simply connect your data sources (like Google Analytics, Shopify, or Salesforce) and use natural language to create reports. You could just ask, "Show me a chart of my cost per sale by campaign for last month," and the dashboard gets built for you in seconds, no DAX required.