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:
Write Average Line: Total Sales divided by Order Lines, using DIVIDE.
Press Run to see the result.
DIVIDE(numerator, denominator). Use the measures in square brackets.
Total Sales = SUM(Sales[Amount])
Order Lines = COUNTROWS(Sales)
Average Line = DIVIDE([Total Sales], [Order Lines]) See why with a store that has no sales in 2024:
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
Write Margin % = Total Margin ÷ Total Sales. Split by category: which category has the best margin?
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Total Cost = SUMX(Sales, Sales[Quantity] * Sales[UnitCost])
Total Margin = [Total Sales] - [Total Cost]
Margin % = DIVIDE([Total Margin], [Total Sales]) 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:
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
- Write
Share of Unitsfor each store: its units ÷ all units. (You’ll need ALL from Lesson 5.3; try it there.) - Why does a “Margin %” calculated column summed in a visual show numbers like 3,000 %?
- Write
Sales per Store= Total Sales ÷ number of stores with sales.
Answers
DIVIDE([Units], CALCULATE([Units], ALL(Store))).- It adds up thousands of row-level percentages. Ratios must be measures.
DIVIDE([Total Sales], DISTINCTCOUNT(Sales[StoreKey])).
Check yourself
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.