Ravindra BagaleCourses & study guides Track your progress

Guides

DIVIDE in DAX — Safe Ratios and Percent Measures

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

DIVIDE is the safe way to build ratios and percent measures in DAX — it avoids divide-by-zero pain from a plain / operator and pairs cleanly with percentage formatting in Power BI Desktop.

Friends! Every KPI board wants a percent. Why do students still write [Sales] / [Orders]? Because Excel habits die slowly. How? We switch to DIVIDE, build AOV and a simple %, and poke an empty City on purpose. FreshBasket (fictional) — calm maths.

Quick answer

Vertical safety checklist:

  1. Prefer DIVIDE ( numerator, denominator ) in measures.
  2. Optional third argument = alternate when denominator is 0.
  3. Format the measure as % when the story is a percent.
  4. Test a slicer value with zero orders.
  5. Use DIVIDE inside % of total patterns too.
  6. Document what blank means for readers.
DIVIDE ( [Total Sales], [Orders] )
DIVIDE ( [Part], [Total], 0 )

Why DIVIDE, rows in / number out

Order ID City Amount
A Mumbai 100
B Mumbai 40
C Nashik 80
  1. Why DIVIDE: ratios. Average order value, online mix %, growth %.
  2. Mumbai rows in: A + B. Sales = 140. Distinct orders = 2.
  3. DIVIDE ( 140, 2 ) → 70. That is AOV for Mumbai.
  4. Plain 140 / 0 blows up when a city has no orders. DIVIDE returns blank (or your alternate).
  5. Online mix for all cities: Online sales 180 / Total 220 → DIVIDE ( 180, 220 ) ≈ 0.818. Format as 81.8%.

Rows go into the base measures. DIVIDE turns two numbers into one safe ratio.

Slash vs DIVIDE Slash can error or show infinity; DIVIDE stays calm. A / B÷0 risk DIVIDE ( A, B )blank or alternate prefer

Prefer DIVIDE ( A, B ) over A / B in measures so empty groups stay blank (or your alternate) instead of exploding.

What do I need before this guide?

  • Base measures like [Total Sales] and an orders count.
  • Course: Aggregation functions / useful functions notes in DAX.

Before and after (look at the tables first)

Before DIVIDE Plain division risk when Orders is zero.

Before

After DIVIDE Safe AOV with DIVIDE; blank on zero.

After

Slash vs DIVIDE

  1. A / B can error or show infinity when B is blank/zero depending on context.
  2. DIVIDE ( A, B ) returns blank by default when B is zero.
  3. Reports stay quiet instead of screaming.

Phone-bill analogy: never divide “talk time” by zero days in a new plan month. DIVIDE is the polite guard.

DIVIDE for ratios

DIVIDE safe division DIVIDE returns blank (or alternate) when the denominator is zero. Numerator DIVIDEsafe / Ratio / % safe

DIVIDE is the safe way to compute ratios and percents in measures — guards against divide-by-zero.

AOV =
DIVIDE (
    [Total Sales],
    [Orders]
)

AOV or Zero =
DIVIDE (
    [Total Sales],
    [Orders],
    0
)
  1. First form: blank when no orders.
  2. Second form: show 0 when that is kinder to the visual.
  3. Pick deliberately — do not flip randomly per page.
  4. On the Mumbai sample, AOV should read 70.

Percents

Percent with DIVIDE DIVIDE then format the measure as a percentage. DIVIDE 0.18 18% format

Build the ratio with DIVIDE, then format the measure as a percentage in Model / Measure tools.

Online Mix % =
DIVIDE (
    [Online Sales],
    [Total Sales]
)
  1. Create the measure.
  2. Measure tools → Format → Percentage.
  3. 0.18 becomes 18% for readers.
  4. Mumbai alone: Online is 100 of 140 → about 71%.

Mistakes and calm fixes

Symptom Likely cause Fix
Infinity / error Used / Switch to DIVIDE
Always blank Denominator always 0 Check filters / measure
1800% shown Forgot % format Format as percentage
Wrong story Numerator ≠ part of denominator Align definitions

Ghabru naka 😅 — blank is often DIVIDE being polite, not broken.

Ravindra Bagale's Tip

A common mistake: students format a measure as % but still compute with slash, then panic on empty cities. Habit: DIVIDE first, format second. Do not forget!

Practice task

  1. Rewrite one slash measure with DIVIDE.
  2. Build AOV and prove Mumbai 70 on the sample idea.
  3. Build Online Mix %.
  4. Slice a City with no Online sales; note blank vs 0.
  5. Try the alternate 0 argument once.

Learn it properly

Course lessons:

Related guides: IF / SWITCH · % of total ALLSELECTED

Got it? DIVIDE for ratios; format for %. Empty groups should not crash your page. Next: year-to-date. Let's continue.

Frequently asked questions

Why DIVIDE instead of / ?

DIVIDE handles divide-by-zero calmly (blank or alternate) so report pages do not show errors or infinity for empty groups.

What is the third argument?

An optional alternate result when the denominator is zero (for example 0).

Does DIVIDE format as percent?

No — it returns a scalar ratio. You set the measure format to percentage in Desktop.

Blank vs zero?

Blank often hides in visuals; zero shows as 0%. Pick what readers expect.

Use in % of total?

Yes — DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], … ) ) is the classic shape.

Course links?

Aggregation / useful functions lessons and base measures in the DAX chapter.