Power BI DAX for Data Analysts — Measures, CALCULATE, Time Intelligence & Best Practices

-- Total Revenue
Total Revenue = SUM(Sales[Revenue])

-- Total Orders
Total Orders = COUNT(Sales[OrderID])
-- or if you want to count distinct orders:
Distinct Orders = DISTINCTCOUNT(Sales[OrderID])

-- Average Order Value
Avg Order Value = AVERAGE(Sales[Revenue])
-- Safer version (avoids divide-by-zero if no orders):
Avg Order Value = DIVIDE(SUM(Sales[Revenue]), COUNT(Sales[OrderID]), 0)

-- Gross Profit Margin %
Gross Margin % =
    DIVIDE(
        SUM(Sales[Revenue]) - SUM(Sales[COGS]),
        SUM(Sales[Revenue]),
        0
    )

-- Calculated Column: Profit per row
Profit = Sales[Revenue] - Sales[COGS]

-- Revenue from a specific category, regardless of page-level filters
Online Revenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        Sales[Channel] = "Online"
    )

-- Revenue from multiple categories
Product Revenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        Sales[Category] IN {"Electronics", "Mobiles"}
    )

-- Remove all filters from a column (ALL function as a filter modifier)
Total Revenue All Regions =
    CALCULATE(
        SUM(Sales[Revenue]),
        ALL(Sales[Region])      -- ignores any slicer on Region
    )

-- Revenue % of total (classic pattern)
Revenue % of Total =
    DIVIDE(
        SUM(Sales[Revenue]),
        CALCULATE(SUM(Sales[Revenue]), ALL(Sales)),
        0
    )

-- Revenue for a specific customer segment, keeping date filters active
Premium Revenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        Customers[Segment] = "Premium"
        -- date filters from slicers are still active
    )

-- Year-to-Date Revenue
Revenue YTD =
    TOTALYTD(SUM(Sales[Revenue]), 'Date'[Date])

-- Quarter-to-Date Revenue
Revenue QTD =
    TOTALQTD(SUM(Sales[Revenue]), 'Date'[Date])

-- Month-to-Date Revenue
Revenue MTD =
    TOTALMTD(SUM(Sales[Revenue]), 'Date'[Date])

-- Previous year revenue (same period)
Revenue PY =
    CALCULATE(
        SUM(Sales[Revenue]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )

-- Year-over-Year growth %
Revenue YoY % =
    DIVIDE(
        SUM(Sales[Revenue]) - [Revenue PY],
        [Revenue PY],
        0
    )

-- Previous month revenue
Revenue PM =
    CALCULATE(
        SUM(Sales[Revenue]),
        DATEADD('Date'[Date], -1, MONTH)
    )

-- Month-over-Month growth %
Revenue MoM % =
    DIVIDE(
        SUM(Sales[Revenue]) - [Revenue PM],
        [Revenue PM],
        0
    )

-- 3-month rolling average
Rolling 3M Avg =
    AVERAGEX(
        DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH),
        [Total Revenue]
    )

-- Step 1: Create a disconnected "Metric Selector" table
-- New table → Enter data:
-- MetricName
-- Revenue
-- Orders
-- Avg Order Value
-- Gross Margin %

-- Step 2: Create the dynamic measure
Selected Metric =
    SWITCH(
        SELECTEDVALUE('MetricSelector'[MetricName], "Revenue"),
        "Revenue",          [Total Revenue],
        "Orders",           [Total Orders],
        "Avg Order Value",  [Avg Order Value],
        "Gross Margin %",   [Gross Margin %],
        [Total Revenue]     -- default if nothing selected
    )

-- Step 3: Add MetricSelector[MetricName] as a slicer on the canvas
-- The chart now updates dynamically based on slicer selection

-- SUMX: sum of (quantity × price) — calculated row by row
Total Revenue =
    SUMX(
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    )
-- This is more accurate than SUM(Sales[Revenue]) when Revenue is not pre-stored

-- AVERAGEX: weighted average discount
Avg Weighted Discount =
    AVERAGEX(
        Sales,
        Sales[DiscountPct] * Sales[Revenue]
    ) / AVERAGEX(Sales, Sales[Revenue])

-- MAXX: most recent order date per customer in a measure context
Latest Order Date = MAXX(Sales, Sales[OrderDate])

-- RANKX: rank products by revenue (1 = highest)
Product Revenue Rank =
    RANKX(
        ALL(Products[ProductName]),
        [Total Revenue],
        ,
        DESC,
        Dense
    )

-- Top N products using RANKX (for filter/table use)
Is Top 5 Product =
    IF([Product Revenue Rank] <= 5, "Top 5", "Other")

-- Without variables — hard to read, calculates revenue twice
Revenue YoY % =
    DIVIDE(
        SUM(Sales[Revenue]) - CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Date'[Date])),
        CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR('Date'[Date])),
        0
    )

-- With variables — clean, readable, CALCULATE runs once
Revenue YoY % =
    VAR CurrentRevenue = SUM(Sales[Revenue])
    VAR PriorYearRevenue =
        CALCULATE(
            SUM(Sales[Revenue]),
            SAMEPERIODLASTYEAR('Date'[Date])
        )
    VAR YoYChange = CurrentRevenue - PriorYearRevenue
    RETURN
        DIVIDE(YoYChange, PriorYearRevenue, 0)

-- Variables in conditional logic
Customer Status =
    VAR TotalSpend = SUM(Sales[Revenue])
    RETURN
        SWITCH(
            TRUE(),
            TotalSpend >= 100000, "Platinum",
            TotalSpend >= 50000,  "Gold",
            TotalSpend >= 10000,  "Silver",
            "Bronze"
        )