Calculations with DAX

Say you run a small coffee company with a few shops. Your sales data lists every order: what it sold for, what it cost you, and the date. Your boss asks a simple question. “What’s our profit margin, and are we beating last year?”

You look at your data. There is no “profit margin” column. There is no “last year” column. Those numbers are not sitting anywhere. They have to be worked out from the columns you do have. Profit margin is profit divided by sales. “Beating last year” means comparing this year’s sales to the same stretch of time twelve months ago.

That working-out is the job of DAX. This chapter shows you how to ask Power BI those questions and get real answers back.

Why this matters

Most of the questions a business actually cares about are not stored as columns. Nobody types your margin into a spreadsheet. Nobody records your year-over-year growth by hand. These are calculations, and they change every time you slice the data a different way. Margin for coffee is different from margin for pastries. Growth in Denver is different from growth in Austin.

DAX is how you build those calculations once and reuse them everywhere. Write “Margin %” a single time, and it works for coffee, for pastries, for one shop, for all shops, for last month, for this year. You do the thinking once. Power BI does the arithmetic forever after, and it redoes it instantly whenever someone clicks a filter.

This is the hardest chapter in the track, so we will go slowly and keep every example concrete. You do not need any math beyond adding and dividing.

What DAX is

DAX stands for Data Analysis Expressions. It is the formula language built into Power BI for creating calculations. A formula language just means a set of words and rules for writing instructions that produce a number (or a bit of text) from your data.

If you have ever typed =SUM(B2:B40) into a spreadsheet, you already understand the idea. DAX is that idea, grown up for larger and better-organized data. One difference matters right away. In a spreadsheet you point a formula at specific cells, like B2 through B40. In DAX you never point at cells. You point at whole columns and tables by name, like Sales[Revenue]. Power BI figures out which rows to include based on what the person looking at the report has filtered.

Throughout this chapter, imagine a table called Sales. Each row is one order. It has a Revenue column (what the order sold for) and a Cost column (what it cost you). You write a column name as the table, then the column in square brackets: Sales[Revenue].

Calculated columns vs measures

DAX gives you two places to put a calculation. Choosing the right one is the single most important decision in this chapter, so read this section twice.

A calculated column is a new column you add to a table. Power BI works it out one row at a time and stores the answer, just like the columns that were already there. The calculation runs once when the data loads (or refreshes), and the results sit in the table until the next refresh.

Here is a calculated column that works out the profit on each individual order:

Line Profit = Sales[Revenue] - Sales[Cost]

Power BI goes row by row. For the first order it does that order’s revenue minus that order’s cost, writes the answer in the new cell, and moves to the next order. When it finishes you have a full column of per-order profit, stored and ready.

A measure is different. A measure does not store anything and does not fill in a column. It is a calculation that runs on the fly, at the moment you look at it, over whatever data is currently in view. Move a slicer, click a bar on a chart, and the measure recalculates for the new selection.

Here is a measure that adds up revenue:

Total Revenue = SUM(Sales[Revenue])

That one line gives you the total for everything, the total for one shop, the total for last March, or the total for pastries. Same measure, different answer, depending on what the report is showing. Measures are the workhorse of Power BI. You will write far more measures than calculated columns.

When to use each:

Use a calculated column when you need a value on every single row, especially to filter, sort, or group by it. “Was this a large order or a small one?” belongs on each row, so it is a calculated column.
Use a measure when you want a rolled-up number that responds to filters: a total, an average, a count, a percentage. Almost everything in this chapter is a measure.

A quick gut check. If the answer is “one number per row,” reach for a calculated column. If the answer is “one number for the group I am looking at,” reach for a measure.

Writing your first measure

Let’s build Total Revenue together. The steps below are the exact clicks in Power BI Desktop.

Try It
In the Data pane on the right, right-click your Sales table and choose New measure. A formula bar appears at the top.
Type this, exactly:
Total Revenue = SUM(Sales[Revenue])
The part before the equals sign is the measure’s name. SUM is a DAX function that adds up all the numbers in a column. Sales[Revenue] is the column it adds.
Press Enter, then click the checkmark to confirm. Your new measure now appears in the Data pane with a small calculator icon.

In the Visualizations pane, click the Card visual (it looks like a single big number). With the card selected, tick the box next to Total Revenue.
You should see your grand total revenue filling the card. Now drag a Region or Shop field onto the report as a slicer and click one option. Watch the card change. You wrote the formula once, and it re-answers itself for whatever you select. That is the whole point of a measure.

The core functions you will actually use

You can get a long way with a handful of functions. Here are the ones worth memorizing, each with a real business use.

SUM adds up a column of numbers.

Total Cost = SUM(Sales[Cost])

AVERAGE gives the arithmetic mean of a column. If every row is one order, the average of the revenue column is your average order value.

Average Order Value = AVERAGE(Sales[Revenue])

COUNTROWS counts how many rows are in a table. If every row is one order, this counts your orders.

Order Count = COUNTROWS(Sales)

Now build on these. A profit measure can reuse the two measures you already made, by name, in square brackets:

Total Profit = [Total Revenue] - [Total Cost]

Referring to a measure by its name in brackets is normal and encouraged. It keeps your formulas short and means a fix in one place flows everywhere.

DIVIDE, and why it beats the slash

To get margin, you divide profit by revenue. Your instinct is to type a slash:

Margin % = [Total Profit] / [Total Revenue]

This works right up until a shop, a day, or a product has zero revenue. Then you are dividing by zero, and Power BI shows an error. One bad cell can splash errors across your whole report.

DAX gives you a safer tool. The DIVIDE function does the same division but handles the zero for you. Its shape is DIVIDE(top number, bottom number), with an optional third value to show when the bottom is zero.

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

If revenue is zero, plain division errors out. DIVIDE quietly returns a blank instead, so your report stays clean. Microsoft’s own guidance is to use DIVIDE whenever the bottom number could be zero or empty.

Set the format of this measure to percentage, and you have a real, reusable Margin % that works for any slice of the business.

CALCULATE, the one that changes the question

Every measure so far answers “for whatever is currently in view.” CALCULATE lets you change what is in view, just for that one calculation. It evaluates an expression in a filter context you specify.

Suppose you want coffee revenue on the same report as total revenue, side by side, no slicer involved. CALCULATE does it:

Coffee Revenue = CALCULATE([Total Revenue], Products[Category] = "Coffee")

Read it left to right. Take Total Revenue, but first apply a filter: only rows where the product category is “Coffee.” The first thing inside the parentheses is the calculation. Everything after a comma is a filter that reshapes what the calculation sees.

CALCULATE is the most important function in DAX because it is how you build comparisons: this category versus all categories, this year versus last year, this region against the company. We use it again in the year-over-year example below.

Row context and filter context, gently

You just met the phrase “filter context.” Along with “row context,” it is the idea that trips up every beginner, so here it is in plain terms with a picture you can hold onto.

Row context means “the current row.” It is what a calculated column works inside. When Power BI computes Sales[Revenue] - Sales[Cost] for order number 3, it is standing on row 3, using row 3’s revenue and row 3’s cost. Then it steps to row 4. Row context is a calculation walking down a table, one row at a time, each row minding its own business.

Filter context means “the slice currently in view.” It is what a measure works inside. A measure does not walk rows. It looks at everything the report has filtered down to (this shop, this month, this category) and calculates over that whole slice at once. When you clicked the Denver slicer earlier, you changed the filter context, and Total Revenue answered for Denver.

Here is the one-sentence version. A calculated column asks “what is true for this row?” A measure asks “what is true for this selection?” Row context is a row. Filter context is a slice. CALCULATE is powerful precisely because it can change the slice.

You do not need to master this today. You need to know the two phrases exist and roughly what each one means, so the words stop feeling like a wall.

A first taste of time intelligence

Comparing to last year sounds hard. DAX has ready-made functions that make it almost easy, as long as one thing is in place.

That one thing is a proper Date table, the kind you set up in chapter 4. A Date table is a separate table with one row per calendar day, marked in Power BI as your official date table. Time functions lean on it to understand what “a year ago” means. Without it, they will not work correctly.

With a Date table in place, here is last year’s revenue for the same period you are looking at:

Revenue Last Year = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))

SAMEPERIODLASTYEAR takes your date column and hands back the matching dates from twelve months earlier. CALCULATE then works out Total Revenue over that shifted stretch of time. Show this next to Total Revenue in a table by month, and each row lines up this year against last year automatically.

Turn that into a growth percentage with DIVIDE:

Revenue YoY % = DIVIDE([Total Revenue] - [Revenue Last Year], [Revenue Last Year])

This is the “are we beating last year?” number your boss asked for. It reads as: the change from last year to this year, divided by last year, which is the standard way to express year-over-year growth. Format it as a percentage and you are done.

In business

Picture the monthly review for the coffee company. On one screen the owner now has:

Total Revenue and Total Cost, which roll up instantly for any shop or month.
Total Profit and Margin %, so a strong sales month with thin margins cannot hide.
Average Order Value and Order Count, which together explain why revenue moved. More orders, or bigger ones?
Coffee Revenue beside total revenue, showing what share the flagship product carries.
Revenue YoY %, the headline number for whether the business is growing.

The owner clicks the Austin shop. Every one of those numbers re-answers for Austin in the same instant, no new formulas required. That is the payoff for the work in this chapter. A small set of well-written measures becomes a report that answers questions faster than anyone can ask them.

Common pitfalls

Building a measure as a calculated column (or the reverse). This is the classic beginner mistake. If you make “Total Revenue” a calculated column, Power BI writes the same grand total onto every single row and it never responds to a filter. If you try to flag “large order” as a measure, it has no single row to judge. Rule of thumb: one-number-per-row is a column, one-number-per-selection is a measure. When in doubt, it is probably a measure.

Dividing with a slash and hitting divide-by-zero. Writing [Profit] / [Revenue] looks fine and tests fine, until real data includes a day or a product with zero revenue. Then errors spread through the report. Use DIVIDE([Profit], [Revenue]) instead. It handles the zero and returns a clean blank.

Expecting a measure to show a number on its own. A measure has no value until it sits in a visual that gives it a filter context. If you write one and nothing happens, that is normal. Drop it into a card or a table and it comes to life.

Time functions failing without a Date table. SAMEPERIODLASTYEAR and its relatives need a real Date table, marked as such in the model. If year-over-year returns blanks or errors, check chapter 4 and confirm your Date table is set up and marked before you blame the formula.

Practice

Work these in your own report. Each one builds on a function from this chapter.

Create Total Revenue, Total Cost, and Total Profit as three measures. Put all three on cards. Confirm that Profit equals Revenue minus Cost.
Create Margin % with DIVIDE and format it as a percentage. Add a shop or region slicer and watch the margin shift as you click between shops.
Create Order Count with COUNTROWS and Average Order Value with AVERAGE. Put them in a table broken down by month. Which month had many small orders, and which had a few large ones?
If you have a Date table set up, create Revenue Last Year and Revenue YoY %. Build a table with month down the side and all three revenue measures across the top. Find your best growth month and your worst.

If a measure errors, read the message, check your column and table names against the Data pane, and confirm that anything time-related is pointed at your marked Date table.

Part of the Power BI track. Developed with AI assistance and reviewed by a human editor. Power BI changes often; menu names, features, and prices may have shifted since the “checked on” dates in this chapter. Verify anything critical against Microsoft’s official documentation.