Power BI DAX DIVIDE
The DIVIDE Function
The DIVIDE function divides one number by another.
When you divide by zero, it returns a blank instead of an error.
Syntax
DIVIDE(numerator, denominator [, alternateresult])
| Argument | Meaning |
|---|---|
| numerator | The number to divide. |
| denominator | The number to divide by. |
| alternateresult | Optional. The value to return when the denominator is zero or blank. If you leave it out, DIVIDE returns a blank. |
This page uses the Poke Mart model described in DAX Introduction, with the measures Total Sales from DAX Measures, and Total Cost and Order Count from DAX Aggregation Functions.
Why Not Just Use / ?
DAX has a division operator, /, just like Excel.
It works fine until the denominator is zero:
| Formula | Result |
|---|---|
| 10 / 2 | 5 |
| 10 / 0 | Infinity |
| 0 / 0 | NaN (not a number) |
| DIVIDE(10, 2) | 5 |
| DIVIDE(10, 0) | (blank) |
| DIVIDE(10, 0, 0) | 0 |
In Excel, dividing by zero gives the #DIV/0! error.
DAX returns Infinity or NaN instead.
In a report, Infinity and NaN look like mistakes to the reader.
A blank is better: by default, visuals leave out the rows where the measures are blank, so the reader only sees rows with data.
In a measure, the denominator is often another measure.
A measure can be zero or blank for some filters, for example for a product that has no sales in a given month. So Microsoft recommends:
- Use DIVIDE when the denominator is a measure or an expression that could be zero or blank.
- Use / when you divide by a constant, like
[Total Sales] / 1000. There, the division can never fail.
Profit
First, one more measure that you need for the margin.
Profit is the sales minus the costs.
There is no division, so a minus sign is all you need:
Example
Profit = [Total Sales] - [Total Cost]
| Measure | Result |
|---|---|
| Profit | 4,292,195 |
Profit uses two measures you already have: Total Sales from DAX Measures and Total Cost from DAX Aggregation Functions.
As the naming convention says, the measures are written without a table name.
Margin %
The profit margin is the share of the sales that is profit: Profit divided by Total Sales.
Example
Margin % = DIVIDE([Profit], [Total Sales])
Format the measure as a percentage: select it, and on the Measure tools tab, set Format to Percentage and the number of decimal places to 2.
| Measure | Result |
|---|---|
| Margin % | 45.58% |
Of every 100 in sales, about 46 is profit.
Avg Order Value
The average order value is the sales divided by the number of orders:
Example
Avg Order Value = DIVIDE([Total Sales], [Order Count])
Format this measure as Decimal number with 2 decimal places and a thousands separator.
| Measure | Result |
|---|---|
| Avg Order Value | 3,436.79 |
Each row of Sales is one order, so AVERAGE(Sales[Amount]) returns the same number.
Building the measure from Total Sales and Order Count makes its meaning clear, and it reuses two measures you already trust.
The Results by Category
Put Category and the measures in a table visual:
| Category | Total Sales | Total Cost | Profit | Margin % | Order Count | Avg Order Value |
|---|---|---|---|---|---|---|
| Balls | 4,447,200 | 2,477,560 | 1,969,640 | 44.29% | 1,035 | 4,296.81 |
| Medicine | 4,413,600 | 2,386,540 | 2,027,060 | 45.93% | 1,110 | 3,976.22 |
| Status Healers | 556,000 | 260,505 | 295,495 | 53.15% | 595 | 934.45 |
| Total | 9,416,800 | 5,124,605 | 4,292,195 | 45.58% | 2,740 | 3,436.79 |
Status Healers are a small part of the sales, but they have the highest margin: 53.15%.
Balls sell the most, but with the lowest margin.
Notice that the Total row of Margin % is not the average of the three percentages.
It is 4,292,195 divided by 9,416,800, calculated over all the rows.
Measures always work this way.
By Year
Replace Category with Year from the Date table:
| Year | Total Sales | Profit | Margin % | Avg Order Value |
|---|---|---|---|---|
| 2024 | 4,393,200 | 1,999,570 | 45.52% | 3,442.95 |
| 2025 | 5,023,600 | 2,292,625 | 45.64% | 3,431.42 |
| Total | 9,416,800 | 4,292,195 | 45.58% | 3,436.79 |
Sales grew by 630,400 from 2024 to 2025, and the margin stayed almost the same.