DIVIDE in DAX: the cleaner division for your Power BI measures

Typing Sales / Target for a percentage feels fast and done. That division works on most days, which is exactly why you only question it when something breaks: ∞ on a card, NaN in a matrix, a report that raises questions instead of answering them. DIVIDE offers a cleaner way to write the same division, with fewer edge cases to handle yourself.

This is part 4 of the DAX basics series on trainerbjorn.nl. Part 3 covered SUM, COUNTROWS, and DISTINCTCOUNT. Part 5, coming up next, covers filter context.

What DIVIDE actually does

DIVIDE produces the same result as the / operator, with one difference in behavior: when the denominator is zero or blank, DIVIDE returns BLANK instead of Infinity or NaN.

The syntax is DIVIDE(numerator, denominator), with an optional third argument for an alternate result when the denominator is blank. That third argument is a deliberate choice, not the default.

In practice: DIVIDE([Sales], [Target]) gives the same percentage as [Sales] / [Target] when a target is present. When no target exists, DIVIDE returns BLANK and the visual shows that cell as empty. The regular / operator returns Infinity or NaN in that situation, and those values flow through to your visuals, cards, and downstream calculations without any extra handling.

Why that matters in a presentation

BLANK behaves differently from zero in Power BI. A measure that returns BLANK for a row disappears from tables and matrices automatically: visuals hide BLANK rows by default, without any extra configuration. That means a product without a target simply does not appear in the table, exactly as intended in a report that people open every morning without someone walking them through it.

A percentage card showing ∞ on a bad day pulls all attention toward that exception, not toward the number people came to see. With DIVIDE, a cell shows a number or shows nothing, and both outcomes are correct and require no explanation. In a report people consult every day, that difference compounds: they trust what they see, or they start questioning the report itself.

How to remember the argument order

The one hurdle with DIVIDE is knowing which value goes where. The shortcut: the numerator sits on top in a fraction, so the numerator comes first in DIVIDE. DIVIDE([Sales], [Target]) reads exactly like a fraction written by hand, numerator on top, denominator underneath, read from top to bottom.

Type it out once with that image in mind, and you will not forget it again, the same way you learned to write a fraction in school.

The third argument: when you actually need it

DIVIDE has an optional third argument, alternateresult, which lets you return a fixed value instead of BLANK when the denominator is blank. That is useful when a KPI card must always display a number, even when data is missing, for example when an executive dashboard is expected to show something at all times.

Do not use it as the default. In a table or matrix, you want BLANK rows to disappear, not to fill with zero. The default BLANK is the cleaner choice for most percentages and ratios, precisely because the visual hides those rows automatically.

Where an empty denominator comes from

An empty denominator can feel like something is broken in the data, but in practice it almost always comes from the filter context of the visual: a period with no sales, a segment with no customers, a slicer combination that returns zero rows at that moment. That is a valid outcome, not a data error. The filter context of the visual determines which rows the measure sees, and when there are no rows for a given combination, an empty denominator is exactly the expected result.

More on this in part 5, which covers filter context: how a visual decides which rows it sees, and why the blank cell in your measure almost never points to a data fault.

The default rule

Every division in a measure goes through DIVIDE by default, unless you have a specific reason to use /. The extra keystrokes are worth it: a measure that handles an empty denominator quietly is a measure you do not have to defend in a presentation.

Want to work through the DAX basics series together, or have a question about a specific measure in your report? Visit trainerbjorn.nl or get in touch.