Mightora / DAX learning library

Power BI DAX calculation examples

27 copy-ready starter measures for Power BI semantic models, including related helper measures, model prerequisites, caveats, formatting guidance and official Microsoft references. DAX is not executed or benchmarked on this page.

Open the interactive DAX Workbench ↗

Explore all 27 patterns

Examples use an illustrative Sales fact table, Date/Product/Region dimensions and optional Targets and Region Selector tables. Adapt them to your own schema, relationships, granularity and business rules. Static examples do not validate a semantic model.

Core measuresBeginner

Total sales

Sum a numeric sales amount column in the current filter context.

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Model prerequisites: Sales fact table with numeric Sales Amount; one row can represent an order line.
Why this approach: A reusable base measure should be referenced by higher-level KPIs rather than repeating SUM throughout the model.
Watch out: Check currency, returns and tax treatment; SUM ignores blank amounts.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Core measuresBeginner

Total cost

Aggregate an amount-valued cost column.

Total Cost =
SUM ( 'Sales'[Cost Amount] )

Model prerequisites: Sales[Cost Amount] stores total line cost, not per-unit cost.
Why this approach: Keep revenue and cost in separate reusable measures.
Watch out: For per-unit cost, use SUMX over quantity and unit cost instead of SUM.
Suggested measure format: Currency

Complete DAX including helper measures

Total Cost =
SUM ( 'Sales'[Cost Amount] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Core measuresBeginner

Distinct order count

Count orders once even when the sales table has multiple lines per order.

Total Orders =
DISTINCTCOUNT ( 'Sales'[Order ID] )

Model prerequisites: Sales[Order ID] is a stable order key; an order may have multiple sales lines.
Why this approach: DISTINCTCOUNT avoids inflating order counts on line-grain fact tables.
Watch out: DISTINCTCOUNT includes one BLANK key if present; validate source completeness or use DISTINCTCOUNTNOBLANK.
Suggested measure format: Whole number

Complete DAX including helper measures

Total Orders =
DISTINCTCOUNT ( 'Sales'[Order ID] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Core measuresBeginner

Purchasing customers

Count distinct nonblank customer keys represented in the selected sales.

Purchasing Customers =
DISTINCTCOUNTNOBLANK ( 'Sales'[Customer ID] )

Model prerequisites: Sales fact rows contain Customer ID; omit customers with missing IDs.
Why this approach: Defines a purchasing-customer metric, distinct from all customers in the dimension table.
Watch out: The measure only sees customers with sales records; it does not count customers with no purchases.
Suggested measure format: Whole number

Complete DAX including helper measures

Purchasing Customers =
DISTINCTCOUNTNOBLANK ( 'Sales'[Customer ID] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Core measuresBeginner

Units sold

Sum quantity across visible sales lines.

Total Units =
SUM ( 'Sales'[Quantity] )

Model prerequisites: Sales[Quantity] represents signed units; returns may appear as negatives.
Why this approach: A base quantity measure is useful for volume and unit-price calculations.
Watch out: Decide whether negative return quantities belong in the headline volume KPI.
Suggested measure format: Whole number

Complete DAX including helper measures

Total Units =
SUM ( 'Sales'[Quantity] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ratios & KPIsBeginner

Gross margin amount

Compute absolute gross margin using reusable base measures.

Gross Margin =
[Total Sales] - [Total Cost]

Model prerequisites: Total Sales and Total Cost use the same financial scope and comparable currency.
Why this approach: Separating margin amount from margin percentage keeps both metrics reusable.
Watch out: Do not mix cost-at-standard-price with actual sales if the business definition requires realised margin.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Total Cost =
SUM ( 'Sales'[Cost Amount] )

Gross Margin =
[Total Sales] - [Total Cost]

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ratios & KPIsBeginner

Gross margin percentage

Use DIVIDE to handle zero or blank revenue safely.

Gross Margin % =
DIVIDE ( [Gross Margin], [Total Sales] )

Model prerequisites: Total Sales and Total Cost are defined at compatible grains.
Why this approach: DIVIDE defaults to BLANK() for a zero or blank denominator; set Percentage format in the model.
Watch out: Do not multiply by 100 when using Power BI Percentage formatting; a zero fallback would change blank semantics.
Suggested measure format: Percentage, 1–2 decimals

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Total Cost =
SUM ( 'Sales'[Cost Amount] )

Gross Margin =
[Total Sales] - [Total Cost]

Gross Margin % =
DIVIDE ( [Gross Margin], [Total Sales] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ratios & KPIsBeginner

Average order value

Divide total sales value by distinct orders, not by order lines.

Average Order Value =
DIVIDE ( [Total Sales], [Total Orders] )

Model prerequisites: Order IDs are unique per order across the filtered dataset.
Why this approach: Using DISTINCTCOUNT for orders avoids understating average order value when orders have several lines.
Watch out: Choose a consistent treatment of cancelled orders, refunds and orders with zero value.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Total Orders =
DISTINCTCOUNT ( 'Sales'[Order ID] )

Average Order Value =
DIVIDE ( [Total Sales], [Total Orders] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ratios & KPIsBeginner

Average realised unit price

Find average revenue per unit, weighted by units sold.

Average Realised Unit Price =
DIVIDE ( [Total Sales], [Total Units] )

Model prerequisites: Sales Amount and Quantity refer to the same transactions and units.
Why this approach: Revenue divided by units is different from a simple average of product prices.
Watch out: Returns, discounts, freight and mixed unit types can alter its meaning.
Suggested measure format: Currency per unit

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Total Units =
SUM ( 'Sales'[Quantity] )

Average Realised Unit Price =
DIVIDE ( [Total Sales], [Total Units] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ratios & KPIsIntermediate

Weighted unit price

Weight unit prices by line quantities with SUMX.

Weighted Unit Price =
VAR WeightedValue =
    SUMX ( 'Sales', 'Sales'[Unit Price] * 'Sales'[Quantity] )
VAR Units =
    SUM ( 'Sales'[Quantity] )
RETURN
    DIVIDE ( WeightedValue, Units )

Model prerequisites: Sales fact rows have a numeric Unit Price and Quantity at compatible line grain.
Why this approach: SUMX evaluates each row's price-times-quantity before aggregating, avoiding averaging prices equally.
Watch out: Use signed quantities consistently; rounding, exchange rates and returns may mean this differs from booked revenue divided by quantity.
Suggested measure format: Currency per unit

Complete DAX including helper measures

Weighted Unit Price =
VAR WeightedValue =
    SUMX ( 'Sales', 'Sales'[Unit Price] * 'Sales'[Quantity] )
VAR Units =
    SUM ( 'Sales'[Quantity] )
RETURN
    DIVIDE ( WeightedValue, Units )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ratios & KPIsIntermediate

Sales against target (%)

Measure variance relative to a sales target using a safe denominator.

Sales Variance to Target % =
DIVIDE ( [Total Sales] - [Sales Target], [Sales Target] )

Model prerequisites: Targets[Sales Target] is additive and aligned to the Date/Product/Region reporting grain via appropriate relationships or allocation logic.
Why this approach: The denominator is the target, so 0.10 means 10% above target.
Watch out: Targets kept at a different granularity often repeat or disappear under slicers; confirm propagation and aggregation.
Suggested measure format: Percentage

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Target =
SUM ( 'Targets'[Sales Target] )

Sales Variance to Target % =
DIVIDE ( [Total Sales] - [Sales Target], [Sales Target] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Core measuresBeginner

Sales target

Aggregate stored target amounts under the model's date and organisational filters.

Sales Target =
SUM ( 'Targets'[Sales Target] )

Model prerequisites: Targets fact table with numeric Sales Target at an agreed time and organisational grain.
Why this approach: Centralises target calculation for plan-versus-actual measures.
Watch out: Date and other dimensions must correctly filter Targets; unaligned targets can be misleading.
Suggested measure format: Currency

Complete DAX including helper measures

Sales Target =
SUM ( 'Targets'[Sales Target] )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceIntermediate

Year-to-date sales

Aggregate sales from the start of the reporting year to the current visible date.

Sales YTD =
CALCULATE (
    [Total Sales],
    DATESYTD ( 'Date'[Date] )
)

Model prerequisites: Continuous Date table related to Sales, marked as a date table for classic date-column time intelligence.
Why this approach: The date table supplies a complete calendar for time-window calculations.
Watch out: This is calendar-year YTD by default; for fiscal years, adapt the DATESYTD year_end_date argument.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales YTD =
CALCULATE (
    [Total Sales],
    DATESYTD ( 'Date'[Date] )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceIntermediate

Month-to-date sales

Return sales from the beginning of the visible month.

Sales MTD =
CALCULATE (
    [Total Sales],
    DATESMTD ( 'Date'[Date] )
)

Model prerequisites: Continuous, related date table used in visuals and slicers.
Why this approach: Keeping a standalone MTD measure avoids repeating time intelligence in each report.
Watch out: The meaning depends on the current date filter context; it does not automatically mean 'through today'.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales MTD =
CALCULATE (
    [Total Sales],
    DATESMTD ( 'Date'[Date] )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceIntermediate

Quarter-to-date sales

Return sales since the beginning of the current calendar quarter.

Sales QTD =
CALCULATE (
    [Total Sales],
    DATESQTD ( 'Date'[Date] )
)

Model prerequisites: Continuous related Date table and calendar-quarter interpretation.
Why this approach: Makes quarter-to-date reporting consistent across visuals.
Watch out: Fiscal quarter definitions may differ from calendar quarters; check your date table and requirements.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales QTD =
CALCULATE (
    [Total Sales],
    DATESQTD ( 'Date'[Date] )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceIntermediate

Sales in previous-year period

Re-evaluate sales for the matching period one year earlier.

Sales Previous Year =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Model prerequisites: Continuous related Date table; time-based filters use Date columns.
Why this approach: A reusable prior-year measure supports YoY difference and growth without duplicated logic.
Watch out: Missing historical dates, incomplete years and period-selection shape affect the comparison.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Previous Year =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceIntermediate

Year-on-year sales growth (%)

Compare the current period with the same period last year.

Sales YoY % =
VAR PreviousSales = [Sales Previous Year]
RETURN
    DIVIDE ( [Total Sales] - PreviousSales, PreviousSales )

Model prerequisites: Date table has a complete calendar and a valid relationship to Sales.
Why this approach: DIVIDE returns BLANK when prior-year sales is zero or unavailable instead of an arbitrary 0% growth.
Watch out: Growth can exceed 100% or be negative; compare equivalent complete periods and test leap-day behaviour.
Suggested measure format: Percentage

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Previous Year =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Sales YoY % =
VAR PreviousSales = [Sales Previous Year]
RETURN
    DIVIDE ( [Total Sales] - PreviousSales, PreviousSales )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceAdvanced

Trailing 12 months sales

Calculate a moving 12-month window ending at the last date in context.

Sales Trailing 12M =
VAR EndDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        DATESINPERIOD ( 'Date'[Date], EndDate, -12, MONTH )
    )

Model prerequisites: Continuous related Date table; Date visual context supplies the as-of date.
Why this approach: Anchoring at MAX(Date) makes the window follow the visual or filter selection instead of hard-coding TODAY().
Watch out: A partial latest month yields a trailing period ending on that day; decide whether you need 12 complete calendar months instead.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Trailing 12M =
VAR EndDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        DATESINPERIOD ( 'Date'[Date], EndDate, -12, MONTH )
    )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Time intelligenceAdvanced

Running sales total in selection

Accumulate the selected date values up to the current point on a visual.

Running Sales =
VAR LastVisibleDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER (
            ALLSELECTED ( 'Date'[Date] ),
            'Date'[Date] <= LastVisibleDate
        )
    )

Model prerequisites: Visuals and filters use the related Date dimension, with a Date-level axis.
Why this approach: ALLSELECTED retains external selection context while broadening the visual's date-point context.
Watch out: ALLSELECTED semantics vary with nested visuals and iterators; this is not an unconditional lifetime total.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Running Sales =
VAR LastVisibleDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER (
            ALLSELECTED ( 'Date'[Date] ),
            'Date'[Date] <= LastVisibleDate
        )
    )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Filter contextIntermediate

Share of selected category total

Show each product category's share within the selected category set.

Category Share % =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        ALLSELECTED ( 'Product'[Category] )
    )
)

Model prerequisites: Product dimension related to Sales; Product[Category] is on the visual axis or matrix.
Why this approach: ALLSELECTED can preserve page and slicer selections while calculating a visual-level comparison.
Watch out: The result depends on visual shape and hierarchy; check totals and cross-filtering before interpreting as market share.
Suggested measure format: Percentage

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Category Share % =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        ALLSELECTED ( 'Product'[Category] )
    )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Filter contextIntermediate

Share across all categories

Remove just the category filter to calculate a category-independent denominator.

Share Across Categories % =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( 'Product'[Category] )
    )
)

Model prerequisites: Product dimension has a Category column and filters Sales.
Why this approach: REMOVEFILTERS makes the denominator independent of Category without discarding Date and other dimension filters.
Watch out: Other filters on Product (such as Product Name) still apply; this is not a universal all-products share.
Suggested measure format: Percentage

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Share Across Categories % =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        REMOVEFILTERS ( 'Product'[Category] )
    )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Filter contextBeginner

Selected region label

Return a region name when precisely one distinct region is in context.

Selected Region =
SELECTEDVALUE ( 'Region'[Region Name], "All / multiple regions" )

Model prerequisites: Region[Region Name] is a text dimension column used by report selections.
Why this approach: SELECTEDVALUE avoids ambiguous MAX/MIN text choices when more than one value is present.
Watch out: The fallback also appears when zero values are selected; it does not distinguish no selection from multiple selections.
Suggested measure format: Text

Complete DAX including helper measures

Selected Region =
SELECTEDVALUE ( 'Region'[Region Name], "All / multiple regions" )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Filter contextIntermediate

Sales of active products

Intersect a product status constraint with any existing report filters.

Sales Active Products =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( 'Product'[Status] = "Active" )
)

Model prerequisites: Product[Status] contains an Active value, and Product filters Sales.
Why this approach: KEEPFILTERS intersects the active-product constraint rather than replacing an existing filter on the same column.
Watch out: If an existing selection excludes Active, the result will be blank; this is intentional intersection behaviour.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Active Products =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( 'Product'[Status] = "Active" )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Ranking & segmentationAdvanced

Product rank (dense)

Rank visible products by sales without ranking the total row.

Product Rank =
IF (
    ISINSCOPE ( 'Product'[Product Name] ),
    RANKX (
        ALLSELECTED ( 'Product'[Product Name] ),
        [Total Sales],
        ,
        0,
        "Dense"
    )
)

Model prerequisites: Product dimension filters Sales; Product Name is unique per product or replace it with a product key.
Why this approach: Dense ranking gives consecutive ranks after ties; ISINSCOPE avoids a misleading grand-total rank.
Watch out: Duplicate product names can merge products; floating-point values may produce unexpected ties; large cardinality can be costly.
Suggested measure format: Whole number

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Product Rank =
IF (
    ISINSCOPE ( 'Product'[Product Name] ),
    RANKX (
        ALLSELECTED ( 'Product'[Product Name] ),
        [Total Sales],
        ,
        0,
        "Dense"
    )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Relationships & modellingAdvanced

Sales by shipping date

Temporarily activate the Ship Date relationship in a measure.

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( 'Sales'[Ship Date], 'Date'[Date] )
)

Model prerequisites: Sales[Ship Date] → Date[Date] existing inactive relationship; another active order-date relationship may also exist.
Why this approach: USERELATIONSHIP applies a specific existing relationship for this calculation without changing the whole model.
Watch out: Check model RLS restrictions and relationship direction; missing/incomplete shipping dates may produce blanks.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( 'Sales'[Ship Date], 'Date'[Date] )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Relationships & modellingAdvanced

Disconnected region selection

Apply a disconnected selector's values as filters on a region dimension.

Sales Selected Regions =
CALCULATE (
    [Total Sales],
    TREATAS (
        VALUES ( 'Region Selector'[Region Name] ),
        'Region'[Region Name]
    )
)

Model prerequisites: Region Selector disconnected from the model; Region table related to Sales; both columns contain compatible names.
Why this approach: TREATAS creates filter propagation from a disconnected selection where no physical relationship exists.
Watch out: Unmatched selector values are ignored. Prefer a real relationship where a suitable model relationship is possible.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Selected Regions =
CALCULATE (
    [Total Sales],
    TREATAS (
        VALUES ( 'Region Selector'[Region Name] ),
        'Region'[Region Name]
    )
)

Official Microsoft reference ↗Try this in DAX Workbench ↗

Relationships & modellingBeginner

Show zero instead of blank

Explicitly show 0 for a blank result when reporting needs it.

Sales Display Zero =
COALESCE ( [Total Sales], 0 )

Model prerequisites: Total Sales already defined as a numeric measure.
Why this approach: COALESCE converts BLANK to zero for display-friendly KPIs.
Watch out: Replacing missing data with zero can be misleading and can cause visuals to show more categories; only use when justified.
Suggested measure format: Currency

Complete DAX including helper measures

Total Sales =
SUM ( 'Sales'[Sales Amount] )

Sales Display Zero =
COALESCE ( [Total Sales], 0 )

Official Microsoft reference ↗Try this in DAX Workbench ↗

Power BI DAX calculation examples

Explore all 27 patterns

Total sales

Total cost

Distinct order count

Purchasing customers

Units sold

Gross margin amount

Gross margin percentage

Average order value

Average realised unit price

Weighted unit price

Sales against target (%)

Sales target

Year-to-date sales

Month-to-date sales

Quarter-to-date sales

Sales in previous-year period

Year-on-year sales growth (%)

Trailing 12 months sales

Running sales total in selection

Share of selected category total

Share across all categories

Selected region label

Sales of active products

Product rank (dense)

Sales by shipping date

Disconnected region selection

Show zero instead of blank

Built by TechTweedie