Written by the Biznerdly Editorial Team. Technical review by Tanzila Tanjim Tusra, a statistics graduate & partially qualified chartered accountant (ICAB) and data analyst.Last verified: July 2026, tested on Excel 365 and Excel 2021.
DAX (Data Analysis Expressions) is the formula language behind Power Pivot. If you already know Excel formulas, the fastest way to learn it is by direct translation: VLOOKUP becomes RELATED, SUMIFS becomes CALCULATE, and COUNTIF becomes COUNTROWS combined with FILTER. Below are 15 working measures mapped directly to the Excel formulas you already use, with a plain-language explanation of why DAX behaves differently.
Most DAX tutorials fall into one of two traps: they're a full paid course, or they're a random list of formulas with no anchor to what you already know. This article does neither. Every measure here is paired with the Excel formula it replaces, tested against a small sample dataset, and annotated with what it actually returns.
Calculated Column vs Measure: the one distinction that matters most
This is the single most common point of confusion for people coming from regular Excel formulas, so it's worth settling before anything else.
- A Calculated Column is computed once per row, when the table refreshes, and the result is stored: exactly like dragging a formula down a column in Excel. Use it when the answer only depends on other values in that same row (e.g.,
Profit = Sales[Revenue] - Sales[Cost]). - A Measure is computed on the fly, every time it's used in a PivotTable, and its answer changes depending on which filters, rows, and columns surround it at that moment. Use it whenever the answer should change based on context: totals, percentages, averages, anything that needs to react to how the report is sliced.
The plain-language test: if the answer would be exactly the same no matter which row of the report you're looking at, it's a Calculated Column. If the answer changes depending on what's filtered or grouped around it, it's a Measure. Total Expense, for instance, is always a Measure: its value legitimately changes depending on whether you're looking at one department or all of them.
Filter context, in plain English
Filter context is simply "everything currently narrowing down which rows are being looked at": the row headers, column headers, slicers, and filters applied to a PivotTable at a given moment. A Measure automatically recalculates for every different filter context it lands in. That's why the same Total Expense measure can show a grand total in one cell and a single department's total in another, without you writing two different formulas.
Row context is different: it's "which single row am I currently evaluating," and it applies inside Calculated Columns and inside iterator functions (the ones ending in X, like SUMX). Confusing the two is the most common source of "why doesn't my measure update per row": the honest answer is usually that a measure isn't supposed to work like a per-row formula, and what's actually needed is a Calculated Column, or an iterator like SUMX inside the measure.
Excel-to-DAX translation table
| Excel formula | DAX equivalent | Notes |
|---|---|---|
| VLOOKUP / INDEX-MATCH | RELATED() | Only works across an existing table relationship: no lookup column needed |
| SUMIF / SUMIFS | CALCULATE(SUM(...), condition) | CALCULATE is the workhorse of DAX: nearly every filtered calculation runs through it |
| COUNTIF / COUNTIFS | CALCULATE(COUNTROWS(...), condition) | COUNTROWS counts table rows, not cells |
| AVERAGEIF / AVERAGEIFS | CALCULATE(AVERAGE(...), condition) | Same pattern as SUMIFS |
| IF | IF() | Same name and logic, but usually written inside a Measure, not dragged down a column |
| Nested IF / CHOOSE | SWITCH() | Far more readable than nested IFs once you have more than two conditions |
| Running total (drag-down formula) | CALCULATE(SUM(...), FILTER(ALL(...), ...<=...)) | Explained in Measure #7 below |
| % of grand total | DIVIDE(x, CALCULATE(x, ALL(...))) | ALL() removes existing filters so the denominator stays the grand total |
| Weighted average (helper column + SUM/SUM) | SUMX() combined with DIVIDE() | SUMX evaluates an expression per row, then sums the results: no helper column required |
For the complete function-by-function reference, see Microsoft's official DAX function reference.
15 DAX measures that replace common Excel formulas
Sample tables referenced below: Sales (Date, Region, Product, Amount, Quantity, CustomerID) and Calendar (Date, Year, Month), related on Date.
1. Basic total (replaces a simple SUM)
Total Sales := SUM(Sales[Amount])How do I calculate a running total in DAX?: start here; this basic measure is the building block for every measure below it.
2. Conditional total (replaces SUMIFS)
East Region Sales := CALCULATE(SUM(Sales[Amount]), Sales[Region] = "East")CALCULATE takes a base expression and one or more filter conditions, and returns the result as if those filters were applied. It is the closest thing DAX has to a universal tool.
3. Count of records (replaces COUNTIF)
Order Count := COUNTROWS(Sales)Combine with CALCULATE for a conditional count: CALCULATE(COUNTROWS(Sales), Sales[Region] = "East").
4. Pulling a related field (replaces VLOOKUP)
Region Long Name := RELATED(Regions[RegionFullName])This only works inside a Calculated Column, and only when a relationship already exists between the two tables: it walks the relationship rather than searching for a match.
5. Distinct customer count
Unique Customers := DISTINCTCOUNT(Sales[CustomerID])There's no clean single-formula Excel equivalent for this without an array formula or a helper column: one of the places DAX is genuinely simpler than Excel.
6. Percentage of grand total
% of Total Sales :=
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)ALL(Sales) strips away the current row/column filters just for the denominator, so it always reflects the grand total, while the numerator still respects whatever's currently filtered.
7. Running total (cumulative sum)
Running Total :=
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Calendar[Date]),
Calendar[Date] <= MAX(Calendar[Date])
)
)This reads as: "sum everything from the start of time up to the current row's date": the DAX equivalent of dragging a =SUM($B$2:B2) formula down a column, but it works correctly in a PivotTable regardless of sort order.
8. Year-over-year comparison
Sales Prior Year := CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))
YoY Growth % := DIVIDE([Total Sales] - [Sales Prior Year], [Sales Prior Year])Requires a proper Calendar table marked as a Date Table in Power Pivot: a step people frequently skip, then wonder why time-intelligence functions error out.
9. Average transaction value (weighted, not a simple average of averages)
Avg Transaction Value := DIVIDE(SUM(Sales[Amount]), COUNTROWS(Sales))Deliberately not AVERAGE(Sales[Amount]) in every case: DIVIDE-of-sums gives you a true weighted average when aggregating across groups, avoiding the classic "average of averages" distortion.
10. Rank within a category
Product Rank by Sales := RANKX(ALL(Sales[Product]), [Total Sales])The Excel equivalent, RANK.EQ, requires the entire comparison range to be visible on the sheet. RANKX ranks correctly inside a PivotTable even as filters change.
11. Conditional flag (replaces nested IF)
Sales Status :=
SWITCH(
TRUE(),
[Total Sales] > 100000, "High",
[Total Sales] > 50000, "Medium",
"Low"
)SWITCH(TRUE(), ...) is a common DAX idiom for readable multi-condition logic: far cleaner than nesting several IFs inside each other.
12. Same measure, filtered to exclude the current selection (replaces a manual "everything except" formula)
Sales Excluding East := CALCULATE([Total Sales], Sales[Region] <> "East")13. Weighted average using SUMX (replaces a SUMPRODUCT/SUM helper-column combo)
Weighted Avg Price :=
DIVIDE(
SUMX(Sales, Sales[Amount]),
SUMX(Sales, Sales[Quantity])
)SUMX evaluates the expression row by row across the Sales table, then sums those row results: the DAX equivalent of SUMPRODUCT, without needing a helper column first.
14. Prior month comparison
Sales Prior Month := CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH))15. Text list of items in the current filter (replaces TEXTJOIN with an IF array)
Products in View := CONCATENATEX(VALUES(Sales[Product]), Sales[Product], ", ")Useful for report subtitles like "Showing: Product A, Product B, Product C" that update automatically as slicers change.
Common mistakes when learning DAX
| Mistake | Why it happens | Fix |
|---|---|---|
| Writing a Measure as a Calculated Column, or vice versa | Both use similar-looking DAX syntax | Apply the plain-language test in the first section: does the answer depend on context? If yes, Measure. |
| Using SUM() inside a row-context calculation and expecting per-row behavior | SUM() always aggregates the whole filtered table, it doesn't work per row like an Excel cell formula | Use an iterator function (SUMX, AVERAGEX, RANKX) when you need row-by-row evaluation |
| Forgetting to mark a Calendar table as a Date Table | Time-intelligence functions like SAMEPERIODLASTYEAR require it and fail silently or with a cryptic error otherwise | Power Pivot window → Design → Mark as Date Table, on a table with one row per calendar date |
| Nesting CALCULATE filters incorrectly, producing unexpected totals | Multiple filter arguments inside CALCULATE combine with AND logic, which surprises people expecting OR | Use FILTER() with explicit OR logic, or separate measures, when conditions should be additive rather than intersecting |
Downloadable resources
- Working Practice Workbook: the Sales/Calendar/Regions sample data with all 15 measures pre-built, so you can inspect and modify them directly. [DOWNLOAD LINK]
Frequently asked questions
What's the DAX equivalent of SUMIFS?
CALCULATE(SUM(Table[Column]), condition1, condition2, ...). CALCULATE applies each condition as a filter to the SUM, the same way SUMIFS applies each criteria range/criteria pair.
Why doesn't my measure update per row?
Measures are designed to respond to filter context, the row/column/slicer combination surrounding them in a PivotTable, rather than behave like a formula dragged down a column. If you genuinely need a per-row, always-the-same-answer calculation, that's what a Calculated Column is for; if you need row-by-row math that then gets aggregated, use an iterator function like SUMX inside the measure.
What's the difference between a calculated column and a measure?
A Calculated Column computes once per row when the table refreshes and stores the result, exactly like a dragged-down Excel formula. A Measure computes on demand, every time it's displayed, and its result changes depending on the current filter context. Use a Calculated Column when the answer only depends on that row's own data; use a Measure when the answer should change based on how the report is sliced.
Do I need to learn all 15 of these measures before I can use Power Pivot?
No. Measures 1–4 (basic SUM, conditional SUM, COUNTROWS, RELATED) cover the majority of everyday reporting. The later measures, running totals, year-over-year, ranking, are worth learning once a specific report actually calls for them.
0 Comments