Downloads
DAXAggregationBeginner

DIVIDE

Divides two numbers and returns a safe result when the denominator is zero or blank.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals