name: dax-power-pivot
description: Expert DAX formula writing and Power Pivot data modeling for Excel. Use when the user needs to create DAX measures, calculated columns, or build Power Pivot data models. Triggers include requests for DAX formulas, time intelligence calculations (YoY, YTD, rolling averages), percentage of total, rankings, budget variance, sales analytics, or any Power Pivot work in Excel.

DAX & Power Pivot Expert

Write DAX formulas and build Power Pivot data models in Excel.

Core Concepts

Measures vs Calculated Columns

Measures compute at query time, respond to filter context, require aggregation. Use for all analytics.

Total Sales := SUM(Sales[Amount])

Calculated columns compute at refresh, consume memory, operate row-by-row. Use only for slicers, relationships, or row-level classification.

Profit = Sales[Revenue] - Sales[Cost]

Rule: Prefer measures. Calculated columns increase file size.

Context

Filter context: Which rows are visible for aggregation. Set by slicers, PivotTable fields, CALCULATE arguments.

Row context: Current row during iteration. Exists in calculated columns and iterator functions (SUMX, FILTER).

Key: Row context does NOT cross relationships—use RELATED. Filter context DOES propagate from dimension to fact tables.

CALCULATE

The most important function. Modifies filter context before evaluation.

CALCULATE(<expression>, <filter1>, <filter2>, ...)

• Boolean filters convert to table filters internally
• ALL/ALLEXCEPT remove existing filters
• Context transition: converts row context to filter context

Common Patterns

Sales Analytics

Total Revenue := SUM(Sales[SalesAmount])
Units Sold := SUM(Sales[Quantity])
Gross Profit := [Total Revenue] - SUMX(Sales, Sales[Quantity] * RELATED(Products[UnitCost]))
Gross Margin % := DIVIDE([Gross Profit], [Total Revenue], 0)
AOV := DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)
Customer Count := DISTINCTCOUNT(Sales[CustomerID])

Year-over-Year

Sales PY := CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
YoY Growth := [Total Revenue] - [Sales PY]
YoY Growth % := DIVIDE([YoY Growth], [Sales PY], 0)

Year-to-Date

Sales YTD := TOTALYTD([Total Revenue], 'Date'[Date])
Sales PYTD := CALCULATE([Sales YTD], SAMEPERIODLASTYEAR('Date'[Date]))

Month-over-Month

Sales PM := CALCULATE([Total Revenue], DATEADD('Date'[Date], -1, MONTH))
MoM Growth % := DIVIDE([Total Revenue] - [Sales PM], [Sales PM], 0)

Rolling Averages

Rolling Avg 3M := 
VAR SumTotal = CALCULATE([Total Revenue], DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))
VAR CountMonths = CALCULATE(DISTINCTCOUNT('Date'[MonthYear]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH))
RETURN DIVIDE(SumTotal, CountMonths, 0)

Percentage of Total

// Of grand total
Pct of Total := DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(Sales)), 0)

// Of selection (respects slicers)
Pct of Selection := DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALLSELECTED(Products)), 0)

Rankings

Product Rank := 
IF(ISINSCOPE(Products[ProductName]),
    RANKX(ALLSELECTED(Products[ProductName]), [Total Revenue], , DESC, DENSE)
)

Budget Variance

Budget := SUM(Budget[Amount])
Variance := [Total Revenue] - [Budget]
Variance % := DIVIDE([Variance], [Budget], 0)

Running Total

Running Total := 
CALCULATE([Total Revenue], FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])))

Data Modeling

Star Schema

• Fact table: Narrow, deep, contains numeric measures and foreign keys
• Dimension tables: Wide, shallow, contains descriptive attributes
• Relationships: One-to-many from dimension to fact, single direction

Date Table Requirements

Time intelligence requires a proper date table:
- Contiguous dates (no gaps)
- Full calendar years (Jan 1 - Dec 31)
- Mark as date table in Power Pivot

Reference Files

• Function reference: See references/functions.md for complete DAX function syntax
• Advanced patterns: See references/advanced.md for virtual relationships, semi-additive measures, parent-child hierarchies, custom fiscal calendars

Best Practices

1. Use variables for readability and performance—computed once, reused
2. DIVIDE over / —handles division by zero gracefully
3. Avoid nested iterators—SUMX inside SUMX creates cartesian products
4. Column filters over table filters in CALCULATE when possible
5. Format with SQLBI conventions—spaces before parentheses, 4-space indent

Formatting Convention

Measure Name := 
VAR CurrentValue = [Base Measure]
VAR PriorValue = CALCULATE (
    [Base Measure],
    DATEADD ( 'Date'[Date], -1, YEAR )
)
RETURN
    DIVIDE ( CurrentValue - PriorValue, PriorValue, 0 )