🎯 Business Scenario
We're analyzing data for "TechCorp Electronics" - a company selling laptops, smartphones, and tablets across different regions. Our goal is to understand sales performance, customer behavior, and regional trends.
📋 Sales Table
| SaleID |
Date |
ProductID |
CustomerID |
Quantity |
UnitPrice |
Region |
📱 Products Table
| ProductID |
ProductName |
Category |
Cost |
👥 Customers Table
| CustomerID |
CustomerName |
Segment |
Country |
🔢 Basic DAX Calculations
1. Total Revenue
Total Revenue =
SUMX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)
Logic: SUMX iterates through each row in the Sales table and multiplies Quantity by UnitPrice, then sums all results. This gives us the total revenue across all sales.
Result: $2,847,500
2. Average Order Value
Average Order Value =
AVERAGEX(
Sales,
Sales[Quantity] * Sales[UnitPrice]
)
Logic: AVERAGEX calculates the average of an expression for each row. Here it calculates the average revenue per transaction.
Result: $18,984
3. Total Profit
Total Profit =
SUMX(
Sales,
(Sales[Quantity] * Sales[UnitPrice]) -
(Sales[Quantity] * RELATED(Products[Cost]))
)
Logic: RELATED function looks up the cost from the Products table using the relationship. We calculate profit as (Revenue - Cost) for each sale and sum them up.
Result: $1,139,000
4. Unique Customers
Unique Customers =
DISTINCTCOUNT(Sales[CustomerID])
Logic: DISTINCTCOUNT counts the number of unique values in a column, giving us the count of unique customers who made purchases.
Result: 25 unique customers
🚀 Advanced DAX Patterns
1. Sales by Category (with CALCULATE)
Laptop Sales =
CALCULATE(
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]),
RELATED(Products[Category]) = "Laptop"
)
Logic: CALCULATE modifies the filter context. It calculates total sales but only for rows where the related product category is "Laptop".
Laptop Sales: $1,245,000
2. Running Total
Running Total =
CALCULATE(
[Total Revenue],
FILTER(
ALL(Sales[Date]),
Sales[Date] <= MAX(Sales[Date])
)
)
Logic: This creates a running total by removing the date filter with ALL(), then applying a new filter that includes all dates up to the current date context.
3. Top 5 Customers by Revenue
Top 5 Customer Revenue =
CALCULATE(
[Total Revenue],
TOPN(
5,
ALL(Customers[CustomerName]),
[Total Revenue]
)
)
Logic: TOPN returns the top N rows based on a specified expression. We get the top 5 customers by revenue and calculate their total revenue.
4. Percentage of Total
% of Total Revenue =
DIVIDE(
[Total Revenue],
CALCULATE([Total Revenue], ALL(Sales)),
0
)
Logic: DIVIDE safely divides two numbers (returns 0 if denominator is 0). We divide current context revenue by total revenue (removing all filters with ALL()).
📅 Time Intelligence
1. Previous Month Sales
Previous Month Sales =
CALCULATE(
[Total Revenue],
PREVIOUSMONTH(Sales[Date])
)
Logic: PREVIOUSMONTH shifts the date context to the previous month. This allows us to compare current month performance with the previous month.
2. Year-to-Date Sales
YTD Sales =
CALCULATE(
[Total Revenue],
DATESYTD(Sales[Date])
)
Logic: DATESYTD returns all dates from the beginning of the year up to the current date in context. This gives us year-to-date totals.
3. Same Period Last Year
Sales SPLY =
CALCULATE(
[Total Revenue],
SAMEPERIODLASTYEAR(Sales[Date])
)
Logic: SAMEPERIODLASTYEAR shifts the date context to the same period in the previous year. Essential for year-over-year comparisons.
4. Growth Rate
YoY Growth % =
VAR CurrentYear = [Total Revenue]
VAR LastYear = [Sales SPLY]
RETURN
DIVIDE(
CurrentYear - LastYear,
LastYear,
0
)
Logic: VAR allows us to store intermediate calculations. We calculate the difference between current and last year, then divide by last year to get the growth percentage.