Power BI DAX Tutorial 2026
35 Formulas with Real Business Examples

Total Sales = SUM(Sales[Amount])

When to use: Adds all values in a column. Responds to all active filters in the report.

Note: Avoid using SUM in a calculated column — use it only in measures. In a column, use row-level arithmetic instead.

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

When to use: Iterates row by row and sums the result of an expression. Use when you need to multiply before summing — do NOT just multiply summary measures.

Note: SUMX is an iterator — it is more flexible than SUM but slightly slower on very large tables.

Avg Order Value = AVERAGEX(Orders, Orders[Revenue])

When to use: Calculates average of an expression row by row. Useful when you want the average of computed values rather than raw column averages.

Order Count = COUNTROWS(Orders)

When to use: Counts the number of rows in a table (or filtered table). Preferred over COUNT for counting records.

Note: Faster than COUNTA or COUNT because it does not scan column values.

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

When to use: Counts distinct values in a column — essential for customer count, unique product count, or unique city count.

Latest Sale = MAX(Sales[SaleDate])

When to use: Returns minimum or maximum value. Commonly used on dates to find the first or last transaction date.

Sales Delhi = CALCULATE(SUM(Sales[Amount]), Sales[City] = "Delhi")

When to use: Evaluates the first argument (the expression) in a modified filter context. Here, it overrides the City filter to always show Delhi sales regardless of slicer selection.

Note: CALCULATE is the only function in DAX that can modify filter context. Everything that uses dynamic filtering ultimately relies on CALCULATE.

Q1 Online Sales = CALCULATE(
  SUM(Sales[Amount]),
  Sales[Quarter] = "Q1",
  Sales[Channel] = "Online"
)

When to use: Multiple filter arguments are combined with AND logic — all conditions must be true. Use FILTER() inside CALCULATE when you need OR logic.

% of Total = 
  DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
  )

When to use: ALL() removes all filters from the specified table or column, letting you calculate a grand total for use in percentage-of-total calculations.

Note: This is one of the most commonly tested DAX patterns in Indian Power BI interviews.

Sales % by City = 
  DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[City]))
  )

When to use: ALLEXCEPT removes all filters except the specified columns — useful for "% within category" calculations where you want to keep some slicers but clear others.

High Value Sales = 
  CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 50000)
  )

When to use: Returns a table with rows matching a condition. Used inside CALCULATE when the filter condition is complex or involves a measure (not just a column value).

Note: FILTER is an iterator — avoid using it on very large tables without a pre-filter. Use column-based filter arguments in CALCULATE when possible for performance.

Product Category = RELATED(Products[Category])

When to use: Fetches a value from a related table (following the many-to-one relationship). Used in calculated columns to bring in dimension attributes from a lookup table.

Note: Only works in calculated columns, not measures. In measures, the relationship is traversed automatically by the filter context.

Orders Per Customer = COUNTROWS(RELATEDTABLE(Orders))

When to use: Returns the related table from the one side of a one-to-many relationship. Used in calculated columns on the "one" side (e.g., Customers table) to aggregate child records.

Selected City = SELECTEDVALUE(Sales[City], "All Cities")

When to use: Returns the single selected value in a filter, or the default if multiple values are selected. Perfect for dynamic titles and conditional formatting based on slicer selection.

City List = VALUES(Sales[City])

When to use: Returns a one-column table of distinct values in the current filter context. Used inside CALCULATE or iterators — not for direct display.

Sales YTD = TOTALYTD(SUM(Sales[Amount]), Dates[Date])

When to use: Calculates year-to-date cumulative total. Requires a properly marked date table in your data model.

Note: If your fiscal year does not start in January, add the optional third argument: TOTALYTD(..., ..., "31-03") for Indian financial year ending March 31.

Sales MTD = TOTALMTD(SUM(Sales[Amount]), Dates[Date])
Sales QTD = TOTALQTD(SUM(Sales[Amount]), Dates[Date])

When to use: Month-to-date and quarter-to-date cumulative totals. Same pattern as TOTALYTD.

Sales LY = CALCULATE(
  SUM(Sales[Amount]),
  SAMEPERIODLASTYEAR(Dates[Date])
)

When to use: Returns the same period in the prior year — used for year-over-year comparison dashboards.

YoY % = 
  DIVIDE(
    SUM(Sales[Amount]) - [Sales LY],
    [Sales LY]
  )

When to use: Combines current year measure and SAMEPERIODLASTYEAR measure to calculate growth rate. Wrap in FORMAT() for display as a percentage.

Note: Always use DIVIDE() instead of "/" to safely handle division-by-zero (returns BLANK instead of an error).

Sales Prev Month = CALCULATE(
  SUM(Sales[Amount]),
  DATEADD(Dates[Date], -1, MONTH)
)

When to use: Shifts a date period by a specified interval. More flexible than SAMEPERIODLASTYEAR — can compare any period offset (months, quarters, years).

FY Sales YTD = CALCULATE(
  SUM(Sales[Amount]),
  DATESYTD(Dates[Date], "31-03")
)

When to use: Defines the YTD range explicitly — essential for Indian financial year reporting (April–March) where TOTALYTD defaults to calendar year.

Note: This is one of the most asked DAX interview questions for finance/BFSI roles in India.

Product Rank = RANKX(
  ALL(Products[ProductName]),
  [Total Sales],
  ,
  DESC,
  Dense
)

When to use: Ranks each row within a specified table based on a measure. ALL() is required to rank across all products regardless of current filter.

Note: RANKX is almost always tested in Power BI interviews in India. Know the parameters: table, expression, value, order (ASC/DESC), ties (Skip or Dense).

Top 5 Customers = 
  TOPN(5, VALUES(Sales[CustomerName]), [Total Sales], DESC)

When to use: Returns a table of the top N rows by a measure. Often used inside CALCULATE or SUMX to aggregate only the top performers.

Sales Band = 
  SWITCH(
    TRUE(),
    [Total Sales] >= 1000000, "Platinum",
    [Total Sales] >= 500000,  "Gold",
    [Total Sales] >= 100000,  "Silver",
    "Bronze"
  )

When to use: Evaluates conditions and returns a result — works like nested IF but much cleaner. SWITCH(TRUE(), ...) is the standard pattern for range-based categorisation.

Growth Label = 
  VAR CurrentSales = SUM(Sales[Amount])
  VAR LastYearSales = [Sales LY]
  VAR Growth = DIVIDE(CurrentSales - LastYearSales, LastYearSales)
  RETURN
    IF(Growth > 0, "▲ " & FORMAT(Growth, "0%"), "▼ " & FORMAT(Growth, "0%"))

When to use: Variables store intermediate results — they improve readability and performance by evaluating sub-expressions once instead of multiple times. Use VAR...RETURN in any complex measure.

Note: DAX variables are evaluated in the filter context where they are defined, not where they are used. This matters inside iterators.

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

When to use: Safe division — returns the third argument (alternate result) instead of an error when the denominator is zero or BLANK. Always use DIVIDE() over "/" in measures.

Safe Revenue = IF(ISBLANK([Total Sales]), 0, [Total Sales])

When to use: Handles BLANK results — converts them to zero for display purposes. Useful for period comparisons where some months may have no data.