Module 9
6 min read
Advanced DAX Patterns
Learn advanced DAX patterns for complex analysis
What You'll Learn
- Running totals
- Ranking products
- Percentage contribution
- Moving averages
Running Total (Cumulative)
Shows cumulative sum over time.
Running Total =
CALCULATE(
[Total Sales],
FILTER(
ALL(Calendar[Date]),
Calendar[Date] <= MAX(Calendar[Date])
)
)
| Month | Sales | Running Total |
|---|---|---|
| Jan | $100K | $100K |
| Feb | $120K | $220K |
| Mar | $80K | $300K |
Ranking
Rank products by sales (1 = best).
Rank = RANKX(ALL(Products), [Total Sales], , DESC)
| Product | Sales | Rank |
|---|---|---|
| Product A | $50K | 1 |
| Product B | $40K | 2 |
| Product C | $30K | 3 |
Percentage Contribution
What % does each product contribute to total?
% Contribution =
DIVIDE(
[Total Sales],
CALCULATE([Total Sales], ALL(Products))
)
| Product | Sales | % Contribution |
|---|---|---|
| Product A | $50K | 42% |
| Product B | $40K | 33% |
| Product C | $30K | 25% |
Moving Average
Smooth out fluctuations with 3-month average.
3 Month Avg =
AVERAGEX(
DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -3, MONTH),
[Total Sales]
)
Quick Reference
| Pattern | Use For |
|---|---|
| Running Total | Cumulative progress |
| RANKX | Top/bottom performers |
| % Contribution | Part vs whole |
| Moving Average | Trend smoothing |
Performance Tips
| Do | Don't |
|---|---|
| Use variables (VAR) | Repeat same calculation |
| Use simple filters | Complex FILTER when not needed |
| Test with small data | Jump to full dataset |
Try This
- Create Running Total measure
- Create Rank measure
- Create % Contribution measure
- Add all to a table visual
- See the patterns in action!
Tip: These patterns take practice. Start with one, master it, then move to the next!