KajoData / Career & business
CALCULATE in Power BI: filters, shares and previous-year sales
Eight orders explain filter replacement, intersections, denominator scope and the role of a calendar in previous-year comparisons.

A report row says North, but its measure displays sales for South. My first reaction might be that something is broken. Yet the expression may be doing exactly what I asked it to do. CALCULATE can replace a filter on a column, including one established by a row in a visual.
I learn this function using a small model and results I can follow by hand. This guide separates adding a condition, replacing a filter, intersecting filters and removing a filter from a denominator. I then introduce a previous-year comparison and explain why CALCULATE can also appear in a calculated column.
Use data you can inspect in full
The synthetic Orders table below has one row per order and a unique OrderID. Sales is numeric. Both date columns use the Date type, without times or missing values. The examples use North and South for regions and Online and Store for channels.
| OrderID | OrderDate | ShipDate | Region | Channel | Sales |
|---|---|---|---|---|---|
| 1 | 2026-01-10 | 2026-01-10 | North | Online | 100 |
| 2 | 2026-01-10 | 2026-01-11 | North | Store | 200 |
| 3 | 2026-01-10 | 2026-01-10 | South | Online | 300 |
| 4 | 2026-01-10 | 2026-01-11 | South | Store | 400 |
| 5 | 2026-01-10 | 2026-01-11 | North | Online | 50 |
| 6 | 2026-01-10 | 2026-01-10 | South | Online | 150 |
| 7 | 2025-01-10 | 2025-01-10 | North | Online | 80 |
| 8 | 2025-01-10 | 2025-01-10 | South | Online | 120 |
I also use a Calendar table with one row for every day from the beginning of 2025 to the end of 2026. Its Date column has unique values, no gaps and no blanks. I mark it as a date table and create an active one-to-many relationship to Orders[OrderDate]. This is the classic date-column approach described in Microsoft’s guidance.
Establish the initial reporting context
For the first examples, a slicer on Calendar[Date] selects January 2026. That leaves six orders, with identifiers from 1 to 6. The previous year’s records exist in the model but sit outside the current selection. Their presence matters when I later calculate a time comparison.
I create two basic measures, each as its own definition:
Sales Total = SUM(Orders[Sales])
Order Count = COUNTROWS(Orders)
On cards, the expected results are sales of 1200 and a count of 6. A table using Orders[Region] should display 350 for North and 850 for South. North contributes 100, 200 and 50; South contributes 300, 400 and 150. This gives me a reference before I introduce any modified filters. Without one, a plausible looking result can conceal a misunderstood scope.
A visual row is different from DAX row context
The North row in a table visual filters the measure to North. That is filter context. Being displayed on a row does not automatically establish row context in the technical DAX sense. The similar wording makes this distinction unnecessarily easy to miss.
Row context occurs in places such as calculated columns and iterators. It provides access to values in the record currently being considered. Filter context determines the scope in which an aggregation is evaluated. Microsoft introduces both ideas in its DAX basics guide.
At the beginning, I write a plain sentence next to each result: which data can this measure currently see? A card sees the six January orders. The North row sees three of them. Naming that scope makes subsequent changes much easier to understand than memorising the expression alone.
Evaluate an expression after changing filters
The CALCULATE documentation defines the function around a modified filter context. Its first argument describes the calculation. Further arguments can introduce conditions or modify existing filters. Which earlier restrictions remain depends on the scope of those arguments.
I add a measure for Online sales:
Online Sales =
CALCULATE([Sales Total], Orders[Channel] = "Online")
With January 2026 selected and no other channel filter, the result is 600. Orders 1, 3, 5 and 6 contribute 100, 300, 50 and 150. The date restriction still applies, so I do not include the previous year’s 80 and 120 even though those records also belong to Online. The function has changed the channel selection without removing the report’s date selection.
Replacing a filter can be intentional
Next I create a measure that selects South on Orders[Region]:
South Sales =
CALCULATE([Sales Total], Orders[Region] = "South")
In a table using Orders[Region], this displays 850 beside North as well. The condition replaces the existing filter on that same column. This can be useful as a reference measure, provided its name makes the intention clear. A generic name such as Sales would reasonably confuse the reader.
The expression has not cleared every report filter. If I also select Channel equal to Online, South Sales becomes 450. January and the channel restriction remain, while the region changes. The exercise uses the exact columns in Orders. A filter arriving from a separate region dimension requires examination of the relationship and the correct place to modify that filter.
Preserve the intersection with KEEPFILTERS
If South should count only when it belongs to the existing selection, I use KEEPFILTERS:
South Within Selection =
CALCULATE(
[Sales Total],
KEEPFILTERS(Orders[Region] = "South")
)
On the North row, the intersection of North and South contains no orders. SUM over that empty scope returns BLANK, so I do not promise a displayed zero. On South, the result remains 850. At the total level it is also 850, because South remains eligible in that broader context.
Both expressions answer reasonable questions. One supplies a fixed region’s sales as a reference. The other respects the existing restriction on that column and narrows it. Before choosing, I ask whether the report user should be able to exclude South through their selection. That decision determines which behaviour matches the intended measure.
Separate rows from orders
Our model has one row per order, so COUNTROWS counts orders. I now check orders shipped on their order date:
Same Day Orders =
CALCULATE(
COUNTROWS(Orders),
Orders[OrderDate] = Orders[ShipDate]
)
Within the selected January, orders 1, 3 and 6 qualify, giving 3. Both compared columns belong to the same table, as required for this Boolean filter form. Neither contains a time or missing value, so equality has precisely the intended meaning in the exercise.
If the source contains product lines, COUNTROWS counts lines. If the fields contain timestamps, equality compares the complete values rather than just the calendar day. Those source changes require a revised definition. Adding CALCULATE does not establish the correct unit of counting or repair a mismatch between the business question and the data grain.

Design the denominator of a share
I want each region’s share of current sales. The numerator should respect the visual’s row. The denominator should ignore region while keeping January and other restrictions. I use REMOVEFILTERS to state that intention explicitly:
Region Share =
DIVIDE(
[Sales Total],
CALCULATE([Sales Total], REMOVEFILTERS(Orders[Region]))
)
For North, the calculation is 350 divided by 1200, approximately 29.17%. South is 850 divided by 1200, approximately 70.83%. Rounding belongs to presentation here. I retain the calculation’s value in the model and format the measure as a percentage.
DIVIDE handles a zero denominator, returning BLANK by default. I avoid replacing that outcome with zero automatically. A share without a valid basis for division may need a different explanation. First I establish what an absent result means and how users should read it in this report.
Check which filters the denominator retains
Selecting Online changes the denominator to 600 because only the region filter was removed. North then contributes 150 and South 450, producing shares of 25% and 75%. This small variation demonstrates why the phrase share of the total is insufficient. A total still has a defined scope.
If a region slicer uses a separate dimension table, clearing Orders[Region] may leave that dimension filter active. In that model I modify the relevant dimension column. I do not copy the exercise’s code without checking where the filter originates and how it reaches the order records.
I also ask whether the denominator should cover all regions or only regions selected by the user. These are different definitions. This guide uses all regions within the remaining filters. Another requirement needs an intentionally designed measure, rather than another arbitrary ALL added until the number seems plausible.
A previous-year comparison needs a calendar
With the prepared Calendar table, I can define a classic previous-year measure:
Sales Previous Year =
CALCULATE(
[Sales Total],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
The slicer still selects January 2026. The measure evaluates the corresponding period in 2025, where the two orders contribute 80 and 120. The result is 200, compared with current sales of 1200. Both values have explicit input records rather than depending on an unavailable workbook from a video.
SAMEPERIODLASTYEAR has special month-end behaviour and differences between classic and calendar-based time intelligence. I therefore avoid describing it as an unconditional subtraction of 365 days. Leap-year dates and unusual calendars require a return to the documented behaviour and the precise reporting definition.
Missing history differs from recorded zero sales
The calendar must cover the required period and the sales source must actually contain historical observations. A measure cannot create missing transactions. If the previous year is absent, a syntactically correct expression does not establish comparability. The report needs to distinguish missing evidence from an observed zero.
I also check how complete the current period is. Comparing part of a month with a complete month can suggest a change caused simply by unequal observation windows. I choose a meaningful interval before interpreting the result. Shifting dates automatically does not make that decision for me.
For this exercise, the whole of each January is compared and the small table is explicitly treated as the complete synthetic source. In a real model I would also show the latest loaded day. Users need to know whether the available observations actually cover the dates selected in the calendar.
CALCULATE can appear in calculated columns
CALCULATE also has applications in calculated columns. The documentation describes a transition from row context to filter context, which allows an aggregation to use restrictions derived from the current record. Its use in DirectQuery calculated columns and RLS rules has separate documented restrictions.
I nevertheless separate these applications when teaching the basics. A measure responds to the report query’s context. A calculated column serves a different role in the model, so I do not treat it as a substitute simply because it shows similar values under one selection. I first specify the behaviour expected when a slicer changes.
I also avoid presenting CALCULATE arguments as a sequence of instructions executed from left to right. That shortcut can mislead readers about dependent filters and context transition. For an unfamiliar case, I return to its documented rules and write down the scope before and after the change.
Expose intermediate results when a number surprises you
For a percentage, I temporarily display three values: numerator, denominator and division result. North in January should show 350, 1200 and approximately 29.17%. With Online selected, these become 150, 600 and 25%. This helps locate a problem in aggregation, filter removal or presentation.
I then inspect the fields used in the visual and slicers. Similar names can belong to different tables. I check relationships, filter direction and the active date relationship. Order-date and shipping-date analyses can easily use different reporting periods without that difference being obvious from the visual’s title.
Only then do I make the expression more elaborate. More nesting can conceal a model issue. I also compare totals with the individual rows, remembering that a measure is evaluated in the total’s context. It is not necessarily calculated by adding the values currently displayed in the visual.
Distinguish the share of value from the share of orders
The same model can answer a question about each region’s share of order count. North has three of the six orders, giving one half. Its share of sales value is lower, approximately 29.17%, because order amounts differ across the regions. Both results are correct for their respective definitions.
I therefore identify the numerator in the measure’s name and the visual’s description. A label such as Region Share leaves too much interpretation to the reader. These measures are particularly easy to confuse when they happen to produce similar results in one month. Small data with deliberately different amounts helps separate the definitions before publication.
With Online selected, North has two of four orders, still half by count, but only one quarter of the value. This is a useful example for a discussion with the report’s owner. I can ask whether they care about the number of cases being handled or the amount of sales. The choice of function follows that decision rather than making it automatically.
Check behaviour beyond the convenient example
After obtaining the expected results for both regions, I select North alone. I consider which measures should respect that selection and which should provide a comparison with South. I also consider the report with no channel restriction and with Store selected. Changing one condition at a time lets me attribute a result to a specific filter decision.
I treat an empty period as a separate case. No matching orders can produce a blank aggregation, affecting percentages and the displayed table. I do not automatically add an alternative result simply to make every cell look occupied. I first decide whether readers must distinguish missing observations from an observed zero and then choose how to present those states.
Finally, I describe the measure without function names. Saying that it represents a region’s sales among all regions within the same channel and period gives me a useful reference. If my explanation consists only of removing ALL, I return to the business question. The report communicates an answer, while the expression helps establish it.
Exercise: predict the result after a selection changes
Start with January 2026 and a table of regions. Predict Sales Total, South Sales and South Within Selection on the North row. Then select Online and explain why these become 150, 450 and BLANK respectively. Your explanation should identify the column whose filter was replaced or intersected.
Next, add Region Share and expose its denominator as a separate helper measure. Explain the difference between a share across all channels and a share within Online. Finally remove the January selection and consider why previous-year records return to the current total. Initial scope matters before the first CALCULATE is applied.
If you need to clarify the report’s interactions, return to filters and slicers in Power BI. I consider a measure understandable when I can identify its input records, retained filters and modified restrictions. A matching number on a single card is a useful starting point, with much more still to explain.
Key takeaways
Can CALCULATE work in a calculated column?
Yes, including for a transition from row context to filter context. Separate restrictions apply to DirectQuery calculated columns and RLS rules.
How does KEEPFILTERS change a condition?
It intersects the condition with an existing filter on the same column. A standard CALCULATE condition replaces such a filter by default.
Why does the share change when I select a channel?
The example denominator clears only the region filter. Other filters, including channel and period, continue to restrict its scope.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
