DIVIDE
Divides two numbers and returns a safe result when the denominator is zero or blank.
DIVIDE(<numerator>, <denominator> [, <alternateResult>])Returns: A number (a scalar). The alternate result, or BLANK, when the denominator is zero or blank.
What it does
DIVIDE is the safe way to divide in DAX. It works like the slash operator, except that when the denominator is zero or blank, it returns BLANK, or a value you choose, instead of an error. Every ratio in a finance model, such as margin percentage, days sales outstanding, collection rate or utilization, should use it. The optional third argument says what to show when there is nothing to divide by.
Syntax and arguments
- numerator: the value to be divided, often a measure such as
[Gross Margin]. - denominator: the value to divide by, such as
[Total Sales]. - alternateResult (optional): what to return when the denominator is zero or blank. If it is left out, the result is BLANK.
Example
The margin report needs gross margin percentage by product category, and some categories have no sales in the selected month.
Gross Margin % =
DIVIDE(
[Gross Margin],
[Total Sales]
)
Where sales are zero, the measure returns BLANK, so the matrix cell is empty rather than showing an error or a misleading zero. The measure builds on two measures already defined, so the business rule lives in one place.
Example: a second, different use
Average sale per customer, showing 0 instead of a blank for customers with no sales:
Avg Sale per Customer =
DIVIDE(
[Total Sales],
[Unique Customers],
0
)
The third argument returns 0 when there are no customers. Use this only where a zero is the right answer. A blank is often better because it hides rows with nothing to report.
Things to watch
- DIVIDE returns BLANK, not zero, when the denominator is zero. Charts and totals skip blanks, so check that a missing value is not hiding a real problem.
- The alternate result can be any value of the right type, but a zero can look like a real margin. Choose it carefully.
- Build the numerator and the denominator as their own measures first, then divide. This keeps each step checkable.
- DIVIDE does not hide errors in the numerator. If the numerator fails, the result still fails.
- A percentage format is a display setting. Format the measure as a percentage in the model, not with FORMAT, so the value stays a number.
In business terms
DIVIDE is used for every ratio that a controller or analyst reads: gross margin percentage, operating expense ratio, current ratio, days sales outstanding, budget utilization and cost per unit. A broken ratio on a month-end pack is one of the most visible mistakes, and DIVIDE prevents it.