DAX for Beginners: 10 Measures Every Report Needs

DAX (Data Analysis Expressions) is the formula language that turns a loaded Power BI model into a report that actually answers questions. The good news: you do not need hundreds of functions. A small set of measures covers most real reports, and once you see the pattern behind them, you can build almost anything. This guide walks through 10 measures every report needs, with the DAX for each and a plain-English explanation of when to reach for it.

First: measures vs. columns

Before writing a single formula, get the difference between a calculated column and a measure straight, because beginners constantly pick the wrong one.

• A calculated column is evaluated once per row when data loads, and the result is stored in the table — like adding a column in Excel. Use it for values you want to group by, filter on, or put on an axis (for example, a "Profit Margin Band" of High/Medium/Low).
• A measure is evaluated on demand, in the context of whatever a visual is showing — its filters, rows, and slicers. It returns one aggregated number. Use it for anything you sum, average, count, or compare.

Rule of thumb: if you want to slice by it, make a column; if you want to aggregate it, make a measure. Measures are also lighter on memory because nothing is stored — the engine computes them at query time. Most of the 10 below are measures.

To create one, right-click a table in the Data pane, choose New measure, and type the formula. Set number formatting on the Measure tools ribbon, not inside the DAX.

The four building blocks: SUM, COUNTROWS, DIVIDE, AVERAGE

These four show up in nearly every report. Get comfortable here and the rest follows.

1. Total with SUM — the workhorse. SUM adds a numeric column across whatever rows are in context.

Total Sales = SUM(Sales[Amount])

Drop this on a card and it shows the grand total. Put it in a bar chart by Region and it returns the sum per region automatically. That re-filtering by visual context is the whole point of measures.

2. Count rows with COUNTROWS — when you want "how many records" rather than "how much."

Order Count = COUNTROWS(Sales)

COUNTROWS counts the rows of a table in the current filter context. Prefer it over COUNT(column): COUNT only counts non-blank values in one column, whereas COUNTROWS counts actual rows — usually what you mean by "number of orders."

3. Safe division with DIVIDE — avoid the / operator in measures. If the denominator is ever zero, plain division returns an error or infinity that can blank out or break a visual. DIVIDE handles it cleanly.

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

DIVIDE returns blank when the denominator is zero, or you can pass a third argument as the alternate result, e.g. DIVIDE([Total Sales], [Order Count], 0). Notice it references other measures — reusing measures keeps your logic in one place and easy to fix.

4. Average with AVERAGE — a straightforward mean of a column.

Average Sale = AVERAGE(Sales[Amount])

Use AVERAGE when each row is already the value you care about. If you need "average per customer" or "average per day," that is a different calculation — divide a total by a distinct count (see #8) rather than use AVERAGE directly. Knowing which average you mean is half the battle.

The most important function: CALCULATE

CALCULATE is the heart of DAX. It evaluates an expression after changing the filter context — this is how you answer "sales, but only for one category."

Online Sales = CALCULATE([Total Sales], Sales[Channel] = "Online")

The first argument is what to compute; everything after is a filter to apply. You can stack filters: CALCULATE([Total Sales], Sales[Channel] = "Online", Sales[Region] = "West"). Almost every advanced measure — time intelligence, % of total, comparisons — is CALCULATE underneath. Learn this one well.

6. Percent of total with ALL. A "% of total" measure compares the current row's value to the grand total, clearing the row/column filter so the denominator stays whole.

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

ALL(Sales) removes filters from the Sales table, so the denominator is the overall total while the numerator respects the current row (e.g., each region). Set the format to Percentage. This pattern — a value over an ALL-cleared version of itself — appears constantly. To clear just one column instead of the whole table, use ALL(Sales[Region]).

Time intelligence: year-over-year and running totals

Time intelligence needs a proper date table: a separate table with one continuous row per day, related to your fact table, and marked as a date table (Table tools → Mark as date table). Without it, these measures return blanks or wrong numbers.

7. Year-over-year with DATEADD. Build two measures so you can show both the prior-year value and the growth rate.

Sales LY = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR))

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

Sales LY recomputes total sales for the same period one year earlier; YoY % expresses the change as a percentage.

9. Running total with a date filter. A cumulative total adds everything from the start of the period through the current date. The classic pattern uses CALCULATE with a date filter built by FILTER.

Running Total Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

MAX('Date'[Date]) is the last date in the current context (the current point on the line). ALL('Date'[Date]) lifts the date filter so we can re-include every date up to that point. The result climbs steadily across a line chart — perfect for cumulative revenue or YTD progress.

Counting people and ranking them: DISTINCTCOUNT and RANKX

8. Distinct count with DISTINCTCOUNT. "How many unique customers?" is not the same as counting rows — one customer can place many orders.

Active Customers = DISTINCTCOUNT(Sales[CustomerID])

DISTINCTCOUNT counts the unique values in a column under the current filter. It powers metrics like unique users, distinct products sold, or active accounts this month. Combine it with the division pattern — DIVIDE([Total Sales], [Active Customers]) — to get revenue per customer.

10. Ranking with RANKX. Ranking turns "who's biggest" into a number you can sort and filter on.

Sales Rank =
RANKX(
    ALL(Sales[Region]),
    [Total Sales],
    ,
    DESC
)

RANKX ranks each region by [Total Sales], with ALL(Sales[Region]) providing the full list to rank against — otherwise each row only sees itself and everything ties at 1. The empty third argument skips the optional value parameter; DESC (the default order) puts the largest first. Use it for top-N tables, leaderboards, or a Rank column beside your KPIs.

A quick reference

Goal	Function(s)	Measure idea
Sum a value	SUM	Total Sales
Count records	COUNTROWS	Order Count
Safe ratio	DIVIDE	Average Order Value
Mean of a column	AVERAGE	Average Sale
Filter a calculation	CALCULATE	Online Sales
Share of whole	DIVIDE + ALL	% of Total Sales
Prior-year compare	CALCULATE + DATEADD	YoY %
Unique count	DISTINCTCOUNT	Active Customers
Cumulative total	CALCULATE + FILTER	Running Total Sales
Ranking	RANKX	Sales Rank

How to practice without breaking your model

Build these in a copy of your file first. Save as a .pbix (the standard single-file format) or, for version control, as a .pbip (the folder-based Power BI Project format). Power BI Desktop is free, so you can experiment safely. A few habits that pay off:

• Reuse measures. Reference [Total Sales] inside other measures rather than re-typing SUM everywhere. Fix it once, fix it everywhere.
• Build a real date table before touching time intelligence — it is the single most common reason YoY measures return blanks.
• Keep a star schema. A clean model — fact tables in the middle, dimension tables around them — makes CALCULATE and filters behave predictably. For more on getting your model right, see our guide to data modeling basics. And if you want the visuals to look as good as the model, our Power BI theme generator builds a matching JSON theme in seconds.

Master these 10 and you'll find most "complex" measures are just combinations of them — a CALCULATE here, a DIVIDE there, an ALL to clear context. DAX rewards pattern recognition far more than memorization.

---

Not ready to wrangle DAX yourself? Instant PowerBI takes your raw CSV or Excel file and aims to send back a finished, branded Power BI report — modeled, measured, and ready to open in free Power BI Desktop. Skip straight to the insights and try it with your data.