Module 4 · Power BI DAX
CALCULATE in depth
How CALCULATE adds, replaces and removes filters, and how to build shares of a total, of a category and of what the user selected.
About 25 minutes
The problem
The regional sales manager for Lagos asks for a table of products, with each product's share of its category: "Within Household, how much is detergent?" Your first attempt, DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( products ) ) ), gives each product's share of all sales, so the shares within a category add up to about a quarter, not 100%. Your second attempt ignores the region slicer the manager has set to Lagos.
Nearly every interesting measure in Power BI is a variation on "the same number, but with different filters". CALCULATE is the function that changes filters, and getting exactly the right ones kept, replaced or removed is the core skill of DAX.
The concept
DAX
CALCULATE ( <expression>, <filter or modifier>, … )CALCULATE takes the current filter context, changes it with its arguments, then evaluates the expression.
Filter arguments replace filters on the same column
DAX
Revenue Wholesale = CALCULATE ( [Revenue], customers[channel] = "Wholesale" )The condition replaces any existing filter on customers[channel] and keeps every other filter (region, date, product). In a table by channel, every row shows the wholesale figure, because the row's own channel filter has been replaced.
To intersect with an existing filter instead of replacing it, wrap the condition in KEEPFILTERS: CALCULATE ( [Revenue], KEEPFILTERS ( customers[channel] = "Wholesale" ) ) shows wholesale revenue on the Wholesale row and blank on the others.
Modifiers remove filters
| Modifier | Removes |
|---|---|
REMOVEFILTERS ( products ) or ALL ( products ) | every filter on the products table |
REMOVEFILTERS ( products[product_name] ) | only the filter on that column |
ALLEXCEPT ( products, products[category] ) | every products filter except category |
ALLSELECTED ( products ) | filters from the visual itself, keeping slicers and page filters |
REMOVEFILTERS is the modern, clearer name for ALL used as a modifier. ALL is also a table function you can iterate; REMOVEFILTERS can only be used inside CALCULATE.
Three kinds of "share"
DAX
% of Total = DIVIDE ( [Revenue], CALCULATE ( [Revenue], REMOVEFILTERS ( products ) ) )
% of Category = DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALLEXCEPT ( products, products[category] ) ) )
% of Selected = DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALLSELECTED ( products ) ) )None of them touches the customers filters, so a Lagos slicer still applies to both the top and bottom of every fraction. That's what the manager needed.
Example
Picture a matrix with products[category] then products[product_name] in Rows, % of Category and % of Total in Values, and a slicer on customers[region] set to Lagos. Here's how % of Category is worked out at each level:
| Row | Numerator | Denominator |
|---|---|---|
| Detergent 900g (12) | Lagos detergent revenue | Lagos Household revenue |
| Household | Lagos Household revenue | Lagos Household revenue (100%) |
| Total | all Lagos revenue | all Lagos revenue (100%) |
On a product row, ALLEXCEPT ( products, products[category] ) removes the product filter and keeps the category, so the denominator is the category total. On the category row the measure divides the category by itself: 100%. On the grand total row there's no category filter to keep, so it's 100% again.
Walkthrough
- Add
Revenue Wholesale,% of Total,% of Categoryand% of Selected, formatted as percentages with one decimal place. - Build the matrix from the example, with a slicer on
customers[region]. Check that% of Categoryadds up to 100% within each category. - Put
customers[channel]in a table with[Revenue]and[Revenue Wholesale]. Every row shows the wholesale number. Then change the measure to useKEEPFILTERSand watch the other rows go blank. - Add a visual-level filter that hides one category.
% of Totalno longer adds up to 100% on the visible rows, but% of Selecteddoes. Use% of Selectedwhen the share must be of what's on screen. - Write this measure for the Lagos manager and put it in a card, with the region slicer cleared:
DAX
Wholesale Share =
DIVIDE ( [Revenue Wholesale], CALCULATE ( [Revenue], REMOVEFILTERS ( customers[channel] ) ) )Practice
Practice
With the region slicer set to Lagos (all dates), what is Wholesale Share? One decimal place.
Practice
Across all regions and dates, what is Detergent 900g (12)'s % of Category (Household)? One decimal place.
Task
5 minWrite a measure % of Region that shows each customer's share of their region's revenue, in a table with customers[region] and customers[customer_name] in the rows. It should still respond to a date slicer. Paste it here.
Your work is checked for
- Named % of Region
- Uses DIVIDE
- Uses CALCULATE for the denominator
- Keeps the region but removes the customer: ALLEXCEPT(customers, customers[region]) or REMOVEFILTERS on the customer columns
- Doesn't remove the Date filters
Challenge
Challenge · optional
Write Revenue Lagos = CALCULATE([Revenue], customers[region] = "Lagos"). In a table by customers[region], filtered to 2026, what does the measure show on the North West row? (A rounded figure is fine.)
More practice
Drill · optional
What share of all revenue (all dates) comes from the Supermarket channel? Use a % of Total measure that removes the customers filters. One decimal place.
Drill · optional
Legal data: relate invoices to matters. Write Overdue = CALCULATE(SUM(invoices[amount_ngn]), invoices[status] = "Overdue"). What share of Commercial litigation billing is overdue? One decimal place.
Check your understanding
Answer every question to check.