Excel like a finance pro.
Pivot tables · Advanced
DAX measures
Formulas that calculate correctly at any level of the pivot, like a true average or a year-over-year change.
When to use it
DAX measures are formulas that calculate at whatever level the pivot shows, so averages, ratios, and year-over-year comparisons come out right in every cell.
The shape of it
- How
With the pivot on the Data Model, right-click the table in the fields pane, Add Measure.
Worked examples
Ratio measure
Right-click the table in the fields pane, Add Measure, Margin % = DIVIDE(SUM(Sales[Revenue])-SUM(Sales[Cost]), SUM(Sales[Revenue])) → A correct margin at every level
Ratios that always work.
True average
Avg Order = AVERAGEX(Sales, Sales[Revenue]) → A true average per order
Row-level math.
Year to date
YTD = TOTALYTD(SUM(Sales[Revenue]), Dates[Date]) → Year to date with a calendar table
Time intelligence.
Worth knowing
- DIVIDE handles divide-by-zero.
- Measures need the Data Model; add a Dates table for time intelligence.
- Format measures once in the measure dialog.
Where it goes wrong
- DAX is a different language with different rules for filter context.
- Measures do not appear in non-model pivots.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.