Ten DAX measures that cover most business reports — totals, averages, percentages, targets and year-to-date — explained in plain English.
What is DAX?
DAX (Data Analysis Expressions) is the formula language of the Data Model in Excel and Power BI. It looks like Excel formulas, but instead of working on cells, it works on whole columns and tables, and the result changes with every filter, slicer and PivotTable row.
Measures vs calculated columns
| Measure | Calculated column | |
|---|---|---|
| Calculated | When the PivotTable needs it, for the current filters | Once for every row, stored in the table |
| File size | No effect | Increases it |
| Use for | Totals, averages, ratios, growth | A value you need per row, such as Profit = Amount − Cost, or a category to slice by |
Rule of thumb: if you will drag it to the Values area, make it a measure.
Where to write a measure
In Excel, go to Power Pivot › Measures › New Measure, pick the table, name the measure and type the formula. Or type Name := formula in the calculation area below a table in the Power Pivot window.
10 measures to start with
These use a Sales table (Date, ProductID, StoreID, CustomerID, Qty, Amount, Cost), a Products table and the Calendar date table from Part 5.
1. Total sales
Total Sales := SUM ( Sales[Amount] )
2. Number of orders
Orders := COUNTROWS ( Sales )
3. Average order value
Avg Order Value := DIVIDE ( [Total Sales], [Orders] )
DIVIDE returns blank instead of an error when the denominator is zero.
4. Unique customers
Customers := DISTINCTCOUNT ( Sales[CustomerID] )
5. Profit and margin
Profit := SUMX ( Sales, Sales[Amount] - Sales[Cost] ) Margin % := DIVIDE ( [Profit], [Total Sales] )
SUMX works row by row, then adds up — no calculated column needed.
6. Sales for one category
Electronics Sales := CALCULATE ( [Total Sales], Products[Category] = "Electronics" )
CALCULATE changes the filter for a measure. It is the most important function in DAX.
7. Percentage of total
% of Total := DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Products ) ) )
ALL removes the product filter, so the denominator is always the grand total.
8. Year-to-date for an April–March financial year
Sales FYTD := TOTALYTD ( [Total Sales], 'Calendar'[Date], "3/31" )
The last argument is the year-end date. Leave it out for a January–December year.
9. Same period last year
Sales LY := CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
10. Year-on-year growth
YoY Growth % := DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
Tips
- Build small measures and reuse them:
[Total Sales]is used in seven of the measures above. - Always write the table name with the column:
Sales[Amount], not just[Amount]. Write measures without a table name:[Total Sales]. This makes formulas easy to read. - Set the number format when you create the measure, so every PivotTable shows it correctly.
FAQ
Do these work in Power BI?
Yes, exactly the same. In Power BI, use Home › New measure and write = instead of :=.
Why does my measure show the same value on every row?
Usually a missing relationship between the table on Rows and the Sales table. Check Diagram View.
Can AI tools write DAX?
Yes, and they are good at it. Give the table and column names, and check the result against a number you already know.