Module 5: Filter context and CALCULATE · Lesson 5.3 · about 18 min
ALL, REMOVEFILTERS and percent of total
Remove filters to get the denominator for share of total. Learn ALL, REMOVEFILTERS, ALLEXCEPT and ALLSELECTED, and when each one gives the percentage people expect.
Module 5 · about 18 minutes · read first: 5.2. You’ll be able to write share-of-total measures that behave correctly with slicers.
1. Why this matters
“What percentage of sales is this?” is asked in every report. The numerator is easy; the denominator needs filters removed, and choosing which filters to remove is the skill.
2. Share of all categories
Write All Categories (Total Sales ignoring any Product filter) and Share = Total Sales ÷ All Categories. Split by Category.
Press Run to see the result.
ALL(Product) inside CALCULATE removes every filter on the Product table.
Total Sales = SUM(Sales[Amount])
All Categories = CALCULATE([Total Sales], ALL(Product))
Share = DIVIDE([Total Sales], [All Categories]) Trace the Home row: CALCULATE removes the filters on Product (and on any table Product looks up), so the denominator sees every Sales row.
REMOVEFILTERS(Product) does the same as ALL(Product) inside CALCULATE and reads more clearly. ALL also works as a table function (a list of all rows or values, ignoring filters), which REMOVEFILTERS doesn’t.
3. Share of my group
Split by Store. Write Region Sales: the sales of the store's whole region, by removing the Store filters with ALL(Store) and putting back the region with VALUES(Store[Region]). Then Share of Region.
Press Run to see the result.
VALUES(Store[Region]) is worked out before the filters change, so on the Falls Centre row it is the list {West}. CALCULATE removes the Store filters, then sets Region to that list.
Total Sales = SUM(Sales[Amount])
Region Sales = CALCULATE([Total Sales], ALL(Store), VALUES(Store[Region]))
Share of Region = DIVIDE([Total Sales], [Region Sales]) On the Falls Centre row, VALUES(Store[Region]) is evaluated first, in the row’s context, and gives {West}. CALCULATE then removes every filter on Store with ALL(Store) and sets Region to West: the denominator is all West stores.
Why not ALLEXCEPT(Store, Store[Region])? It keeps a filter on Region only if there is one. With just Store on the rows, the only filter is on Store[Store], so ALLEXCEPT removes it and you get the whole chain. ALLEXCEPT works when Region itself is in the visual or a slicer, for example a matrix with Region and Store on the rows. The ALL + VALUES pattern works either way.
4. ALL or ALLSELECTED?
A slicer keeps only the West region. Compare Share of All (ALL) with Share of Visible (ALLSELECTED). Which one adds up to 100 % in this visual?
Press Run to see the result.
With the slicer on West, ALL(Store) divides by the whole chain, so West stores add up to about 49 %. ALLSELECTED(Store) divides by what the visual shows (the West stores), so they add up to 100 %. Ask the manager which one they mean: “share of the company” or “share of what I’ve selected”.
Principle
Principle: share = (measure in the current context) ÷ (same measure in a wider context). The function you pick decides how wide:
| Denominator | Removes | Keeps |
|---|---|---|
ALL(Table) |
all filters on the table (and tables it looks up) | everything else |
ALL(Table[Col]) |
filters on that column | everything else |
ALLEXCEPT(Table, Table[Col]) |
all filters on the table except that column | that column, other tables |
ALLSELECTED(Table) |
filters added by the visual itself | slicers and outside filters |
REMOVEFILTERS() |
every filter in the model | nothing |
5. Worked example
Category share by region. In a matrix with regions on rows and categories on columns, Share of Region = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product))) shows, for each region, how its sales split across categories: each row adds up to 100 %. Swap to ALL(Store) and each column adds up to 100 % instead, which answers “where are Home products sold?”.
6. Practice
- Write
Share of Yearfor months: each month’s sales ÷ its year’s sales. - Why can’t you use ALL(Sales[Amount]) for share of total?
- Which denominator would you use for a pie chart that must always add to 100 % whatever slicers are set?
Answers
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL('Date'), VALUES('Date'[Year]))).- It removes filters on the Amount column only, but the visual filters Product or Store, not Amount, so nothing changes.
- ALLSELECTED on the pie’s category table.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.