Home / Power BI Tutorial / Day 6
Day 6 — The measures you will write every day
Eight functions that cover the large majority of real reports.
The aggregators
Every measure on this page runs against the model below — a fact table and three dimensions, the same shape you would build in Power BI Desktop.
Each takes one column and one column only. SUM(Sales[Amount], Sales[Qty]) is not valid DAX — it is not Excel.
Counting
| Function | Counts |
|---|---|
| COUNTROWS(Table) | Rows. Takes a table, not a column |
| COUNT(Column) | Non-blank values in that column |
| DISTINCTCOUNT(Column) | Different values |
Prefer COUNTROWS
COUNT(Sales[Amount]) skips blanks, so if one amount is missing your order count is quietly one short. COUNTROWS counts rows regardless — which is what "how many orders" actually means.
DIVIDE, not the slash
Always use DIVIDE
Line three divides by zero and returns blank instead of an error. Written as
SUM(...) / 0 it throws. In a matrix, one division by zero in one cell breaks the whole visual — and there is always a row somewhere with no denominator.The third argument sets what to return instead of blank, when zero reads better than an empty cell.
Percentages
Read the total row carefully
Computers run at 26.5% margin and Accessories at 40.0%, but the total is 28.6% — not the average of the two, and not their sum. The total row is recalculated with no category filter at all: total margin over total sales. That is correct, and it is the first time you meet the idea that a total row is its own calculation. Day 8 makes this explicit.
IF and SWITCH
SWITCH(TRUE, condition, result, ...) is the DAX way of writing a chain of IFs. It reads far better than three nested IFs and is the standard idiom.
Rounding and formatting
FORMAT returns text
Which means the result can no longer be sorted numerically, plotted on an axis, or used in further arithmetic. Use FORMAT for a card or a label. For everything else, leave the number alone and set the format in the visual instead.
Putting it together
Five measures, one visual. North sells the most overall; look at the average unit price column before deciding which region is really performing best.
Try these yourself
- Write a measure for total cost.
- Count how many different regions appear in the Sales table.
- Work out the average quantity per order.
- Explain why DIVIDE is safer than the / operator.
- Break Sales and Orders down by Product[Product].