Excel & Power BI for Finance: DAX Fundamentals: Measures, CALCULATE, Filter Context

Download the AtlasParts dataset and the Episode 10 Power BI starter file — follow along in your own copy of Power BI Desktop: github.com/GoCelesteAI/excel-powerbi-for-finance.

We have eight AtlasParts Tables loaded, four of them connected through a clean star schema, and a Calendar Table marked as the date dimension. Now it’s time to write our first measures.

DAX is the language Power BI uses to calculate things. It looks a lot like Excel formulas at first glance — SUM, IF, COUNT, the same surface vocabulary — and that resemblance is genuinely helpful for the first ten minutes. After that, DAX starts behaving in ways Excel formulas never did, and the reason is filter context.

This episode is about getting the foundation right: the difference between a calculated column and a measure, your first three measures on AtlasParts, what filter context actually is, and why CALCULATE is the most important function in the entire language.

Calculated columns versus measures

The first decision DAX asks you to make is where a calculation belongs. There are two homes for any DAX expression: a calculated column or a measure. They look the same in the formula bar, but they evaluate at completely different times.

A calculated column runs once, at model load. It adds a new column to a Table, computed row-by-row. The values are stored in Vertipaq alongside the original columns. If you write Profit = gl_journal[debit_amount] - gl_journal[credit_amount] as a calculated column, you’ll get a new column on every GL row, computed at refresh time, sitting in memory.

A measure runs every time a visual asks for it, recalculated under whatever filter context the user has applied. Measures don’t add columns to Tables — they live in the model as named formulas. If you write Total Debits = SUM(gl_journal[debit_amount]) as a measure, nothing gets stored. The formula runs the moment a card visual or table needs the number, against whatever rows the filter context has narrowed down to.

The rule of thumb: if the value depends on context (a filter, a slicer, a date range), make it a measure. If it’s intrinsic to the row (a status label, a derived ID), it can be a calculated column. In practice, finance models lean heavily toward measures. Almost everything you actually want — total revenue, gross margin, year-to-date this, prior-year that — depends on what’s been filtered, so it’s a measure.

Your first measure

The simplest possible measure is a sum. Open the model, right-click on gl_journal in the Data pane, choose New Measure, and type:

Total Debits = SUM(gl_journal[debit_amount])

That’s it. One line. Drop it on a card visual and you’ll see the total of all debits across all rows of the GL — a number in the millions. Now drag a slicer for customers[region] onto the page. As soon as you click a region in the slicer, the card updates. The same measure now shows the total debits for just that region. No code changed. The filter context did the work.

This is the magic of DAX: write the measure once, and it adapts to wherever it’s used. A pivot table in Excel does something similar, but Excel pivots are tied to a specific data source and a specific row/column layout. A DAX measure works in any visual, against any slicer combination, in any direction.

The natural next step is to write Total Credits the same way, and Net as the difference:

Total Credits = SUM(gl_journal[credit_amount])
Net Movement  = [Total Debits] - [Total Credits]

Notice that Net Movement references the other two measures by name in square brackets. DAX measures can call other measures, so once Total Debits and Total Credits exist, Net Movement is just arithmetic.

Filter context, the concept that makes DAX hard

When a card visual displays Total Debits, the visual is asking DAX a question. The question isn’t “what does this measure compute?” — DAX already knows that. The question is “compute it under these filters.”

The filters come from everywhere. From slicers the user has clicked. From rows or columns of a matrix that the visual is laid out in. From cross-filtering when the user clicks a bar in another chart. From the page filter pane. From the report filter pane. All of these get combined into a single thing called the filter context that DAX evaluates the measure under.

The mental model is: every measure runs in a filter context. The filter context narrows the rows of every Table down to just the ones that match. Then the measure does its arithmetic on what remains.

When you put Total Debits on a card with no slicers, the filter context is empty — no filters, all rows visible — so the sum is the grand total. When you click a region, the filter context now includes “customers.region equals North America”. The customers Table is filtered. The relationship from customers to gl_journal propagates the filter — gl_journal is now narrowed to only rows whose customer_id matches a North American customer. SUM runs on that narrowed set. The card updates.

This is why the star schema we built last episode matters so much. Without those relationships, the customer-region filter would never reach gl_journal, and the measure would silently return the unfiltered total. With them, every visual on the page automatically respects every filter the user applies.

Filter context is also what makes some measures behave in ways you wouldn’t expect. A simple SUM at the grand-total row of a matrix shows one number. The same measure at each subtotal row shows a different number, because the filter context at each subtotal row narrows the data to just that group. That’s not a bug. That’s the model working correctly.

CALCULATE — the most important function

Eventually you’ll want a measure that ignores the user’s filter — or applies a different one. For example: a measure that always shows year-to-date revenue regardless of which date the user has selected. Or a measure that shows what would happen if we filtered to only the East region, no matter what the slicer says.

CALCULATE is the function that modifies filter context. Its first argument is an expression to evaluate; everything after that is a filter to add, remove, or replace.

The classic finance example is a same-store comparison: revenue from new customers versus revenue from existing ones. Suppose customers[is_new] is a flag column. You can write:

Revenue from New Customers =
CALCULATE([Total Revenue], customers[is_new] = "Y")

What this does: take the existing filter context (whatever the user has clicked), then add the filter customers[is_new] = "Y" on top, then evaluate [Total Revenue] under that combined context. The result: revenue narrowed to just new-customer rows.

The reverse — revenue from anyone except new customers — uses the same pattern:

Revenue from Existing Customers =
CALCULATE([Total Revenue], customers[is_new] = "N")

CALCULATE can also remove filters. The function ALL(customers) strips every filter from the customers Table, no matter what the slicer says:

Total Revenue (All Customers, ignore slicer) =
CALCULATE([Total Revenue], ALL(customers))

Now that measure shows the same total no matter which region the user selects. Useful for percent-of-total measures: divide the filtered measure by the unfiltered one.

CALCULATE is the most-used function in DAX once you’re past basic sums. Most non-trivial measures involve a CALCULATE somewhere because most non-trivial measures need to modify filter context.

SUM versus SUMX

Sometimes the column you want to sum doesn’t exist yet. Suppose AtlasParts has a sales fact Table with quantity and unit_price as separate columns, and you want total revenue. The naive approach is to add a calculated column for revenue = quantity * unit_price and then sum that. It works, but it bloats the model — every row of the fact gets a new column stored in Vertipaq.

The better approach is an iterator. SUMX walks every row, computes an expression for each row, and sums the results. No new column is stored.

Total Revenue =
SUMX(sales_order_lines, sales_order_lines[quantity] * sales_order_lines[unit_price])

SUMX evaluates quantity * unit_price for each row, then sums the per-row results. Same answer as a calculated column, but the multiplication happens at query time and nothing gets stored.

The X-suffix family includes AVERAGEX, MAXX, MINX, COUNTX, RANKX — all iterators. Use them when the calculation needs to happen row-by-row before the aggregation. Use plain SUM, AVERAGE, etc. when the column you want already exists.

A common rookie mistake is using SUM(quantity * unit_price) and getting a syntax error. SUM takes a single column reference, not an expression. You need SUMX for the row-wise calculation.

Variables make DAX readable

A measure with three CALCULATE blocks and a divide-by-zero check is hard to read in one line. DAX has a clean way to split it: the VAR ... RETURN block.

Gross Margin % =
VAR Revenue = [Total Revenue]
VAR Cost    = [Total Cost]
VAR Margin  = Revenue - Cost
RETURN
    DIVIDE(Margin, Revenue, 0)

Each VAR computes a value and binds it to a name. RETURN gives back the final expression. Variables are evaluated once and reused — so even if a measure is referenced three times, the variable computes it once.

Variables also make debugging easier. If a measure is wrong, you can comment out the RETURN and RETURN Revenue instead — instantly see what the intermediate value looks like in your visuals. Then put the original RETURN back when the value matches what you expect.

Use variables freely. Any measure with more than three lines is clearer with variables.

What we did, what’s next

By the end of this episode you should have a handful of working measures on AtlasParts: Total Debits, Total Credits, Net Movement, maybe a Total Revenue if you wired up the sales fact. You should be able to drop any of them on a card, drop a region or product slicer next to it, and watch the number update correctly.

That’s the foundation. The next episode goes after time intelligence — year-to-date, month-over-month, prior-year variance, the questions every finance team gets asked every Monday morning. Time intelligence is where the Calendar Table from Episode 9 finally pays off.

Then Episode 12 puts everything together — Power Query loads, star schema models, DAX measures — into a real CFO board pack. Revenue trend, gross margin by region, AR aging, budget versus actual, all on one page that publishes to Power BI Service and refreshes nightly.

For homework: open the Episode 10 starter file, write the four measures from this episode (Total Debits, Total Credits, Net Movement, and Revenue from New Customers using CALCULATE), and put each on a card with a region slicer next to it. If your numbers make sense to a finance person — they should — you’ve got the foundation right.

DAX is a deep language. We’ve covered maybe ten percent of what it can do. But these ten percent are what most finance dashboards actually use day to day. The other ninety is for cases that show up rarely and warrant a Stack Overflow search when they do.

See you in Episode 11 — time intelligence, finally.