DAX Formulas Guide for Excel Power Pivot (2026)
Home›Excel›Power Pivot›DAX Formulas
ExcelSub-Guide⏱ 13 min read

DAX Formulas Guide for Excel Power Pivot

DAX (Data Analysis Expressions) is Power Pivot's formula language — Excel-like at first glance, but built for tables and filter context instead of cells. This guide covers the essential DAX functions and patterns you'll use in almost every Power Pivot model.

How DAX differs from Excel formulas

  • No cell references. DAX works over tables and columns. There is no A1 or B2.
  • Column references use table syntax. Sales[Amount] — table name in front, column in brackets.
  • Filter context matters. The same measure returns different values in different PivotTable positions. This is the point.
  • Iterator functions exist. SUMX, AVERAGEX, MAXX loop over rows to compute row-level values before aggregating.

Aggregation functions

Total Sales = SUM(Sales[Amount]) Total Rows = COUNTROWS(Sales) Distinct Products = DISTINCTCOUNT(Sales[ProductID]) Average Order = AVERAGE(Sales[Amount]) Max Order = MAX(Sales[Amount]) Min Order = MIN(Sales[Amount])

Iterators — the X functions

Regular SUM aggregates a single column. Iterators like SUMX loop over rows, compute a value per row, then aggregate.

Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

Iterators: SUMX, AVERAGEX, COUNTX, MAXX, MINX, RANKX

When to use SUM vs. SUMX

If you're summing an existing column: SUM(Sales[Amount]). If you need to compute per-row math before summing: SUMX(Sales, Sales[Qty] * Sales[Price]). Never wrap SUM in SUMX — it doesn't do what you think.

Filter functions

CALCULATE — the central function

West Sales = CALCULATE([Total Sales], Sales[Region] = "West")

CALCULATE evaluates its first argument (an expression) under a modified filter context (subsequent arguments). It's the mechanism behind most non-trivial DAX.

FILTER — build a filtered table

High Value Sales = CALCULATE( [Total Sales], FILTER(Sales, Sales[Amount] > 1000) )

ALL — remove filters

Total Everything = CALCULATE([Total Sales], ALL(Sales)) % of Total = DIVIDE([Total Sales], [Total Everything])

ALLEXCEPT — remove all filters except specified

% of Category = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Products, Products[Category])) )

Relationship functions

RELATED — pull a column from the "one" side

Product Category = RELATED(Products[Category])

Used in calculated columns on the "many" side of a one-to-many relationship.

RELATEDTABLE — pull rows from the "many" side

Order Count = COUNTROWS(RELATEDTABLE(Sales))

Used in calculated columns on the "one" side to count related rows on the "many" side.

Time intelligence

These functions require a proper Calendar table marked as Date Table.

Sales YTD = CALCULATE([Total Sales], DATESYTD(Calendar[Date])) Sales QTD = CALCULATE([Total Sales], DATESQTD(Calendar[Date])) Sales MTD = CALCULATE([Total Sales], DATESMTD(Calendar[Date])) Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date])) Sales LM = CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, MONTH)) YoY Growth = DIVIDE([Total Sales] - [Sales LY], [Sales LY]) YoY Growth % = FORMAT([YoY Growth], "0.0%")

Logical functions

Bucket = IF([Amount] > 1000, "Large", IF([Amount] > 100, "Medium", "Small")) Segment = SWITCH(TRUE(), [Amount] > 1000, "Large", [Amount] > 100, "Medium", "Small") Product Type = IFERROR(RELATED(Products[Type]), "Unknown")

SWITCH with TRUE() is cleaner than nested IFs for multi-branch logic.

Text functions

Full Name = Customers[FirstName] & " " & Customers[LastName] Upper Region = UPPER(Sales[Region]) Product Code = LEFT(Products[SKU], 3) Contains West = SEARCH("West", Sales[Region], 1, 0) > 0

Number and math

Rounded = ROUND(Sales[Amount], 2) Absolute = ABS(Sales[Delta]) Divided Safe = DIVIDE(Sales[Amount], Sales[Quantity], 0)

DIVIDE returns 0 (or a specified alternative) on divide-by-zero. Much safer than /.

Date functions

Year = YEAR(Sales[Date]) Month = MONTH(Sales[Date]) Weekday = WEEKDAY(Sales[Date], 2) Days Between = DATEDIFF(Sales[OrderDate], Sales[ShipDate], DAY)

Common patterns

Percentage of parent category

% of Category Sales = DIVIDE( [Total Sales], CALCULATE([Total Sales], ALL(Products[Product])) )

Ranking within a group

Product Rank = RANKX( ALL(Products[Product]), [Total Sales], , DESC, Dense )

Running total by date

Sales Running Total = CALCULATE( [Total Sales], FILTER( ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date]) ) )

Top N filter

Top 10 Sales = CALCULATE( [Total Sales], TOPN(10, Products, [Total Sales]) )

New customers this month

New Customers = VAR CurrentMonth = MAX(Calendar[Date]) VAR MonthCustomers = CALCULATETABLE( DISTINCT(Sales[CustomerID]), DATESMTD(Calendar[Date]) ) VAR PriorCustomers = CALCULATETABLE( DISTINCT(Sales[CustomerID]), Calendar[Date] < STARTOFMONTH(Calendar[Date]) ) RETURN COUNTROWS(EXCEPT(MonthCustomers, PriorCustomers))

Variables

Use VAR and RETURN to break complex expressions into named parts:

Growth Rate = VAR CurrentSales = [Total Sales] VAR PriorSales = [Sales LY] VAR Diff = CurrentSales - PriorSales RETURN DIVIDE(Diff, PriorSales)

Variables improve readability, prevent recalculation (each VAR is computed once), and make debugging easier.

Common pitfalls

Wrapping SUM in SUMX

SUMX(Sales, SUM(Sales[Amount])) is a common mistake. SUMX iterates row-by-row; wrapping SUM inside creates a Cartesian effect. If you need row-level math, use SUMX(Sales, Sales[Qty] * Sales[Price]).

Forgetting DIVIDE

Using / can produce infinity or errors on zero denominators. Always prefer DIVIDE() in DAX — it handles the zero case cleanly.

Time intelligence without a Calendar table

SAMEPERIODLASTYEAR, DATESYTD, and similar require a Calendar with contiguous dates and Date Table marking. Without one, they return blank or wrong values silently.

Use SWITCH(TRUE(), ...) for multi-branch logic

Nested IFs get unreadable fast. SWITCH(TRUE(), condition1, result1, condition2, result2, default) is far cleaner.

Related guides