DAX Formulas

// MEASURE - Aggregates based on context
Total Sales = SUM(Sales[Amount])

// CALCULATED COLUMN - Fixed value per row
Profit Margin = Sales[Profit] / Sales[Revenue]

// Sum
Total Revenue = SUM(Sales[Revenue])

// Count
Order Count = COUNTROWS(Orders)

// Distinct Count
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

// Average
Avg Order Value = AVERAGE(Orders[Amount])

// Min / Max
First Order Date = MIN(Orders[OrderDate])
Latest Order Date = MAX(Orders[OrderDate])

// Conditional Count
Active Customers = COUNTROWS(
    FILTER(Customers, Customers[Status] = "Active")
)

// CALCULATE modifies filter context
// Syntax: CALCULATE(expression, filter1, filter2, ...)

// Sales for specific category
Electronics Sales = CALCULATE(
    SUM(Sales[Amount]),
    Products[Category] = "Electronics"
)

// Sales for multiple conditions
US Electronics Sales = CALCULATE(
    SUM(Sales[Amount]),
    Products[Category] = "Electronics",
    Customers[Country] = "USA"
)

// Remove filters with ALL
Total Sales All Products = CALCULATE(
    SUM(Sales[Amount]),
    ALL(Products)
)

// Percentage of total
% of Total = DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales)),
    0
)

// Keep certain filters, remove others
% of Category = DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(
        SUM(Sales[Amount]),
        ALLEXCEPT(Products, Products[Category])
    ),
    0
)

// Requires a proper Date table!

// Year to Date
Sales YTD = TOTALYTD(
    SUM(Sales[Amount]),
    'Date'[Date]
)

// Month to Date
Sales MTD = TOTALMTD(
    SUM(Sales[Amount]),
    'Date'[Date]
)

// Quarter to Date
Sales QTD = TOTALQTD(
    SUM(Sales[Amount]),
    'Date'[Date]
)

// Same Period Last Year
Sales LY = CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

// Year over Year Growth
YoY Growth =
VAR CurrentYear = SUM(Sales[Amount])
VAR LastYear = CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN DIVIDE(CurrentYear - LastYear, LastYear, 0)

// Previous Month
Sales PM = CALCULATE(
    SUM(Sales[Amount]),
    PREVIOUSMONTH('Date'[Date])
)

// Rolling 12 Months
Sales Rolling 12M = CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -12,
        MONTH
    )
)

// SUMX - Sum of expression evaluated row by row
Total Revenue = SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

// AVERAGEX
Avg Revenue Per Customer = AVERAGEX(
    VALUES(Sales[CustomerID]),
    [Total Revenue]
)

// MAXX / MINX
Highest Customer Revenue = MAXX(
    VALUES(Customers[CustomerID]),
    [Total Revenue]
)

// COUNTX with condition
Orders Above 100 = COUNTX(
    Orders,
    IF(Orders[Amount] > 100, 1, BLANK())
)

// RANKX
Customer Rank = RANKX(
    ALL(Customers),
    [Total Revenue],
    ,
    DESC,
    DENSE
)

// Variables improve readability and performance
Profit Margin % =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Profit = TotalRevenue - TotalCost
RETURN
    DIVIDE(Profit, TotalRevenue, 0)

// Complex calculation with variables
Customer Lifetime Value =
VAR AvgOrderValue = AVERAGEX(
    VALUES(Orders[OrderID]),
    CALCULATE(SUM(Orders[Amount]))
)
VAR OrderFrequency = COUNTROWS(Orders) /
    DATEDIFF(MIN(Orders[Date]), MAX(Orders[Date]), MONTH)
VAR CustomerLifespan = 24 // months
RETURN
    AvgOrderValue * OrderFrequency * CustomerLifespan

// IF
Status = IF([Total Sales] > 10000, "High", "Low")

// Nested IF
Performance =
IF([Growth] > 0.1, "Excellent",
    IF([Growth] > 0, "Good",
        IF([Growth] > -0.1, "Fair", "Poor")
    )
)

// SWITCH (cleaner than nested IF)
Performance = SWITCH(
    TRUE(),
    [Growth] > 0.1, "Excellent",
    [Growth] > 0, "Good",
    [Growth] > -0.1, "Fair",
    "Poor"
)

// AND / OR
High Value Active =
IF(
    AND([Total Sales] > 10000, [Status] = "Active"),
    "Yes",
    "No"
)

// COALESCE (first non-blank)
Display Name = COALESCE(
    Customers[Nickname],
    Customers[FirstName],
    "Unknown"
)