DAX & Power Query M Formula Reference

Total Sales =
SUM ( Sales[SalesAmount] )

• SUM aggregates a single existing numeric column.
• Use an iterator such as SUMX when an expression must be evaluated row by row.

Blue Sales =
CALCULATE (
    [Total Sales],
    Product[Color] = "Blue"
)

• The first argument is the expression/measure to evaluate.
• Later arguments modify filter context.
• CALCULATE also performs context transition when row context exists.

Category % =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Category] ) )
)

• The numerator stays in the current category.
• Only Category is removed from the denominator.
• Date, Region, Customer and other unrelated filters can remain.

Red Sales (keep) =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Product[Color] = "Red" )
)

• Without KEEPFILTERS, a same-column CALCULATE filter normally replaces the existing one.
• KEEPFILTERS asks DAX to intersect instead.

Sales by Year Only =
CALCULATE (
    [Total Sales],
    ALLEXCEPT ( 'Date', 'Date'[Year] )
)

• Filters on other Date-table columns are removed.
• The Year filter is retained.
• Filters from unrelated tables are not automatically removed.

Revenue =
SUMX (
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

• SUMX creates row context over Sales.
• The expression is calculated for each row and then summed.

Average Customer Sales =
AVERAGEX (
    VALUES ( Customer[CustomerID] ),
    [Total Sales]
)

• VALUES returns distinct customers in current filter context.
• AVERAGEX evaluates [Total Sales] for each customer and averages those totals.

High Value Sales =
CALCULATE (
    [Total Sales],
    FILTER ( Sales, Sales[SalesAmount] > 1000 )
)

• FILTER evaluates its condition row by row and returns a table.
• Use simple Boolean CALCULATE filters when they can express the requirement more directly.

Margin % =
DIVIDE ( [Profit], [Revenue] )

• DIVIDE safely handles zero/blank denominators.
• An optional third argument can define the alternate result.

Shipped Sales =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( 'Date'[Date], Sales[ShipDate] )
)

• The inactive relationship is used for this calculation.
• The model's active relationship is not permanently changed.

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

• Returns a prior-year date set.
• Requires a sound date model for reliable time-intelligence behavior.

Previous Month Sales =
CALCULATE (
    [Total Sales],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

• -1 shifts backward one interval.
• MONTH can be replaced with other supported intervals as needed.

YTD Sales =
TOTALYTD ( [Total Sales], 'Date'[Date] )

• Uses the Date column to build a year-to-date window.
• Other dimension filters such as Product or Region remain unless removed.

// Calculation item
CALCULATE (
    SELECTEDMEASURE(),
    DATESYTD ( 'Date'[Date] )
)

• SELECTEDMEASURE refers to the measure currently being transformed by a calculation group.
• DATESYTD applies the YTD date set.

EVALUATE
SUMMARIZECOLUMNS (
    Product[Category],
    "Sales", [Total Sales]
)

• EVALUATE returns a table expression in a DAX query.
• SUMMARIZECOLUMNS groups results and can include measures.

UserAccess[UPN] = USERPRINCIPALNAME()

• Returns the signed-in user's principal name in the Power BI Service.
• Use a mapping/security table and relationships so the allowed rows propagate correctly.

Table.SelectRows(
    Source,
    each [Status] = "Active"
)

• Source is the input table.
• each evaluates the predicate for each row.
• Supported source operations may fold back to the data source.

Table.SelectColumns(
    Source,
    {"OrderDate", "ProductKey", "Amount"}
)

• The second argument is a list of column names to retain.
• Removing unused columns early can improve refresh and model efficiency.

Table.AddColumn(
    Sales,
    "Revenue",
    each [Quantity] * [UnitPrice],
    type number
)

• The new column is evaluated per row.
• The final argument explicitly sets the result type.

Table.Group(
    Sales,
    {"Region"},
    {{"Sales", each List.Sum([Amount]), type number}}
)

• Rows are grouped by Region.
• The aggregation creates one result per group.
• Grouping changes the table's stored grain.

Table.NestedJoin(
    Sales, {"CustomerID"},
    Customer, {"CustomerID"},
    "Customer", JoinKind.LeftOuter
)

• The key columns should have compatible data types.
• LeftOuter retains all rows from the left table plus matches.

Table.Combine ( {January, February, March} )

• Combines rows from the listed tables.
• Append is a vertical operation; merge is a key-based horizontal operation.

Table.SelectRows(
    Source,
    each [TransactionDate] >= RangeStart
        and [TransactionDate] < RangeEnd
)

• RangeStart and RangeEnd are Date/Time parameters.
• Using >= start and < end creates adjacent, non-overlapping boundaries.
• Preserve query folding where supported.