All lessons

Module 4: DAX basics · Lesson 4.2 · about 14 min

DIVIDE, ratios and percentages

Ratios are where many reports go wrong. Use DIVIDE to avoid errors, build measures from other measures, and learn why a ratio of totals is not the average of ratios.

Module 4 · about 14 minutes · read first: 4.1. You’ll be able to write ratio measures that are correct for any filter.

1. Why this matters

Managers think in ratios: margin %, average order value, conversion rate, share of sales. A ratio that’s wrong at the total row is the fastest way to lose a manager’s trust.

2. DIVIDE

DIVIDE(numerator, denominator, [alternate]) returns blank (or the alternate) when the denominator is 0 or blank. Always use it for measures:

Try it: write the DAX

Write Average Line: Total Sales divided by Order Lines, using DIVIDE.

Press Run to see the result.

See why with a store that has no sales in 2024:

Try it: write the DAX

Run this. Then split by Store[Store]: Forks Market has no 2024 sales. Compare what / and DIVIDE give for it.

Press Run to see the result.

The / version shows Infinity for Forks Market (its 2024 sales are blank), which would break a chart; DIVIDE leaves it blank. (Use / only inside iterators where you know the denominator is never zero; it is slightly faster.)

3. Measures built on measures

Try it: write the DAX

Write Margin % = Total Margin ÷ Total Sales. Split by category: which category has the best margin?

Press Run to see the result.

Each measure is a building block. Total Margin reuses Total Sales and Total Cost; Margin % reuses both. If finance changes how cost is worked out, you change Total Cost and every measure above it follows.

Principle

Principle: a ratio for a group is the ratio of the group’s totals:

Margin %group=∑rMarginr∑rSalesr≠1n∑rMarginrSalesr

The right side is the average of line percentages, which gives a $5 line the same weight as a $500 line. A measure computes both sums in the current filter context first, then divides, so it’s right at every level, including the total row.

Try it: two lines, sales 10 and 90, margins 5 and 9. Ratio of totals: 14/100 = 14 %. Average of ratios: (50 % + 10 %)/2 = 30 %. Which describes the business? (14 %.)

4. Worked example

Average order line value by category. Average Line = DIVIDE([Total Sales], [Order Lines]). Electronics has the highest (the earbuds are $89), Grocery the lowest. At the total row the measure divides total sales by total lines, about $64, which is not the average of the four category averages. That’s correct: categories with more lines carry more weight.

5. Practice

  1. Write Share of Units for each store: its units ÷ all units. (You’ll need ALL from Lesson 5.3; try it there.)
  2. Why does a “Margin %” calculated column summed in a visual show numbers like 3,000 %?
  3. Write Sales per Store = Total Sales ÷ number of stores with sales.
Answers
  1. DIVIDE([Units], CALCULATE([Units], ALL(Store))).
  2. It adds up thousands of row-level percentages. Ratios must be measures.
  3. DIVIDE([Total Sales], DISTINCTCOUNT(Sales[StoreKey])).

Check yourself

1What does DIVIDE([A], [B]) return when [B] is blank or 0?
2Margin % for a category should be…
3Why build Margin % from [Total Margin] and [Total Sales] instead of writing it all in one formula?

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.