Interview Insights · Power BI
The Power BI Question That Ninety Percent of Candidates Fumble
Quick summary — Power BI % of total
The fumble is not about DIVIDE; it is about deciding exactly which filters the denominator should ignore. Almost everyone can write a percentage measure that works on an unfiltered page. The trouble starts when a slicer is applied. ALL on the whole table ignores the slicer, so percentages no longer add to 100%. ALL on one column removes only that column's filter. ALLSELECTED respects filters from slicers outside the visual. Interviewers ask this question because the right answer depends on what the business wants, and only someone who understands filter context can ask the right clarifying question.
In this guide you will learn:
- The exact question and the denominator choices candidates make
- How ALL, REMOVEFILTERS and ALLSELECTED behave when a slicer changes
- Which clarifying question to ask before writing the measure
- Follow-ups on ranking, running totals and year-over-year change
- A test routine to prove your measure in a table visual
SECTION 01The question, and why the first answer usually breaks
I usually set it up like this. There is a Sales fact table, a Product dimension with Category, and a Region dimension. On the page is a table visual with Category and Total Sales, and a Region slicer. Add a column showing each category's share of total sales.
Most candidates write a measure quickly and test it with nothing selected in the slicer. It shows sensible percentages that add up to 100%. Then I select North in the slicer. In many cases the percentages drop and add up to something much smaller. The candidate has used ALL on the entire table, so the denominator still includes every region.
| Denominator used | Filters it removes | With Region = North | Do rows add to 100%? |
|---|---|---|---|
| CALCULATE([Sales], ALL(Sales)) | Every filter on the Sales table and its related dimensions | Total of all regions | No, they add to North's share of all sales |
| CALCULATE([Sales], ALL('Product')) | Every filter from the Product table | North total, all categories | Yes, for North |
| CALCULATE([Sales], REMOVEFILTERS('Product'[Category])) | Only the Category column filter | North total, all categories | Yes, for North |
| CALCULATE([Sales], ALLSELECTED('Product'[Category])) | Category filter from the visual, keeps outer selections | Total of selected categories in North | Yes, of what the user selected |
Total Sales = SUM ( Sales[Amount] )
Category % (Breaks) =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALL ( Sales ) )
)
No slicer selection:
Bikes 60% Accessories 25% Clothing 15% = 100%
Region slicer = North (North is a third of sales):
Bikes 20% Accessories 8% Clothing 5% = 33%
Why: ALL ( Sales ) removes every filter that reaches
the Sales table, including the Region filter coming
through the relationship. The denominator is still
the whole company.Category % =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Product'[Category] ) )
)
Region slicer = North:
Bikes 61% Accessories 24% Clothing 15% = 100%
What to say:
'The numerator is sales for this category under all
active filters. For the denominator, I remove only
the Category filter, so the Region slicer still
applies. The result is each category's share within
whatever region the user picks.'
REMOVEFILTERS ( 'Product'[Category] ) and
ALL ( 'Product'[Category] ) behave the same here.
REMOVEFILTERS states the intent more clearly.The example percentages above are illustrative, not from a real dataset. What matters is the pattern: with ALL on the fact table, the percentages shrink when a slicer is applied, because the denominator never changes.
SECTION 02ALLSELECTED, and the clarifying question to ask first
The strongest candidates do not start typing straight away. They ask a clarifying question: if the user filters the visual to only some categories, should the remaining ones add up to 100%, or should they show their share of all categories?
That question matters because both answers are reasonable. A sales manager comparing a few selected categories may want them to add to 100%. A finance team may want each category's share of the full business, even when only some are shown. ALLSELECTED handles the first case; REMOVEFILTERS handles the second.
| Business wants | Function to use | Behaviour when user picks 2 of 3 categories | Typical use |
|---|---|---|---|
| Share of everything in the current region | REMOVEFILTERS('Product'[Category]) | Two rows that add to less than 100% | Contribution to region total |
| Share of only what is selected | ALLSELECTED('Product'[Category]) | Two rows that add to 100% | Comparing a chosen subset |
| Share of the whole company always | REMOVEFILTERS('Product'[Category], Region) | Ignores region and category selections | Company-wide contribution |
| Share of the parent subcategory | REMOVEFILTERS on SubCategory only | Each product's share within its subcategory | Drill-down matrices |
Category % of Selected =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALLSELECTED ( 'Product'[Category] ) )
)
Setup: Region slicer = North,
Category slicer = Bikes, Clothing
Category % (REMOVEFILTERS):
Bikes 61% Clothing 15% -> adds to 76%
Category % of Selected (ALLSELECTED):
Bikes 80% Clothing 20% -> adds to 100%
ALLSELECTED removes the filter coming from the
visual's rows, but keeps the selections made in
slicers and filters outside the visual.Interviewer: Add a % of total column.
Candidate:
'Before I write it, one question. If the user
selects only some categories in a slicer, should
those add up to 100%, or should each show its
share of all categories?
And should the Region slicer change the total,
so we see shares within North, or should the
total always be the whole company?'
Interviewer: Within the selected region, and
selected categories should add to 100%.
Candidate: 'Then ALLSELECTED on Category is the
right denominator. I will test it with two slicer
selections to confirm.'Asking this question takes fifteen seconds, and it changes how the interviewer sees the rest of your answer. You are no longer a candidate trying to recall a formula. You are an analyst clarifying a requirement, which is the actual job.
SECTION 03The follow-ups that use the same filter logic
Once the percentage works, I usually ask one or two follow-ups that rely on the same skill: changing the filter context for one part of a calculation while keeping the rest. If you understood the percent-of-total answer, these feel natural. If you memorised it, they feel like new problems.
The three that come up most are ranking categories, a running total over dates, and year-over-year growth. Each one removes or shifts a filter in a specific way.
| Follow-up | Filter change needed | Key function | Common slip |
|---|---|---|---|
| Rank categories by sales | Compare against all categories, not just the current row | RANKX over ALL('Product'[Category]) | Ranking over the current row only, so every rank is 1 |
| Running total by date | Include all dates up to the current one | CALCULATE with FILTER over ALL('Date'[Date]) | Forgetting to remove the date filter first |
| Year-over-year growth % | Shift the date filter back one year | DATEADD on a marked Date table | Using the fact table's date column |
| Share of the best category | Compare each category with the maximum | MAXX over ALLSELECTED categories | Hard-coding the top category |
Category Rank =
RANKX (
ALL ( 'Product'[Category] ),
[Total Sales],
,
DESC,
DENSE
)
Running Sales =
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
[Total Sales],
FILTER (
ALL ( 'Date'[Date] ),
'Date'[Date] <= CurrentDate
)
)
-- For a running total within the current year only,
-- use TOTALYTD ( [Total Sales], 'Date'[Date] ).Sales PY =
CALCULATE (
[Total Sales],
DATEADD ( 'Date'[Date], -1, YEAR )
)
YoY Growth % =
DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )
Requirements to say out loud:
- 'Date' is a continuous date table, one row per day
- It is marked as a date table
- It is related to Sales[OrderDate], one-to-many
- Visuals use 'Date'[Year] and 'Date'[Month],
not columns from the Sales table
If Sales PY is blank (first year), DIVIDE returns
blank instead of an error.Notice the ALL inside RANKX. Without it, the ranking table contains only the category on the current row, so every category ranks first. This is the ranking version of the same mistake people make with percentages, just in the other direction.
In Uncodemy mock interviews, the follow-up we use most after this question is the RANKX one, precisely because it tests whether the candidate understands why the filter must be removed.
SECTION 04Test routine: prove your percent-of-total measure
Use this routine on any percentage or ranking measure before you call it done, in an interview or at work. It takes about two minutes and catches most filter mistakes.
BUILD: table visual with Category, Total Sales,
your % measure, and a total row turned on.
ADD: Region slicer and Category slicer.
Test 1 No selections -> % adds to 100%?
Test 2 Region = North -> % adds to 100%?
Test 3 Region = North+South-> % adds to 100%?
Test 4 Category = 2 of 3 -> adds to 100%, or not?
Is that what the business asked for?
Test 5 Check one row by hand:
category sales / denominator = shown %?
Write down what each test showed.WHICH DENOMINATOR?
Share within current region, all categories
-> REMOVEFILTERS ( 'Product'[Category] )
Selected categories add to 100%
-> ALLSELECTED ( 'Product'[Category] )
Share of whole company, ignoring all slicers
-> REMOVEFILTERS ( Sales ) or ALL ( Sales )
Share within parent in a matrix
-> REMOVEFILTERS on the child level only
Always ask first: percent of WHAT?WRITE, THEN TEST EACH ONE
1. Each region's share of company sales
2. Each product's share within its category
3. Each month's share of its year
4. Selected cities that add up to 100%
5. Rank of each product within its category
6. Running total of sales by month
7. YoY growth % by category
8. Each salesperson's share of their team
For each: name the filter you remove or shift,
and the slicer test that proves it.SECTION 05Habits that stop the percent-of-total fumble
These habits apply to every DAX measure that compares a value with a total, rank or earlier period.
- Ask 'percent of what?' — Clarify whether slicer selections should change the total before you write the denominator.
- Remove the narrowest filter — Prefer removing one column's filter over ALL on the fact table, unless the business truly wants the company total.
- Test with a slicer selected — A percentage that looks right with nothing selected proves very little. Always test with at least one selection.
- Turn on the total row — In a table visual, the total row for a share measure should usually show 100%. If it does not, investigate.
- Name measures by intent — Names like 'Category % of Region' tell everyone, including the interviewer, which total you meant.
SECTION 06Interview Q&A — Power BI % of total
Q1What is the difference between ALL and ALLSELECTED in DAX?
ALL removes filters from the specified table or columns completely, ignoring slicers and visual filters. ALLSELECTED removes filters coming from the current visual's rows and columns but keeps the filters the user applied through slicers and filters outside the visual. So ALLSELECTED gives the total of what the user can see.
Q2What is REMOVEFILTERS and how is it different from ALL?
REMOVEFILTERS removes filters from the given table or columns when used as a CALCULATE modifier. In that role it behaves like ALL, but it states the intent more clearly. ALL can also be used as a table function that returns rows, while REMOVEFILTERS cannot.
Q3Why does my percent of total not add up to 100% when I use a slicer?
Usually because the denominator removes the slicer's filter as well, for example with ALL on the fact table. The numerator respects the slicer but the denominator does not, so percentages shrink. Remove only the filter you need, such as the Category column, or use ALLSELECTED.
Q4Why does RANKX return 1 for every row?
Because the table argument contains only the current row's item. Inside a visual, a category row filters Product to one category, so ranking over VALUES or the filtered table gives a single item. Use ALL('Product'[Category]) or ALLSELECTED to rank across all categories.
Q5How do you calculate year-over-year growth in Power BI?
Create a previous-year measure with CALCULATE and DATEADD or SAMEPERIODLASTYEAR on a marked date table, then divide the difference by the previous year with DIVIDE. The visual must use fields from the date table so the time shift works correctly.
SECTION 07Test yourself — Power BI % of total quiz
Five questions. No sign-up.
0 / 5Pick an answer to see why it is right or wrong.
SECTION 08Frequently asked questions
How do I calculate percentage of total in Power BI?
Divide the measure by the same measure evaluated with one filter removed: DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS('Product'[Category]))). Choose which filter to remove based on what the total should mean, and test with slicers.
When should I use ALLSELECTED in Power BI?
Use ALLSELECTED when the total should reflect what the user has selected in slicers and filters, such as making selected categories add up to 100%. It removes filters coming from the visual itself but keeps outer selections.
What DAX questions are commonly asked in Power BI interviews?
Frequent ones include percent of total, CALCULATE and filter context, ALL versus ALLSELECTED, SUMX versus SUM, RANKX, running totals, year-over-year growth and distinct counts. Many interviews add a slicer or filter to test your understanding.
Why is my Power BI percentage wrong after filtering?
The denominator is probably removing more or fewer filters than intended. Check which function you use inside CALCULATE and test the measure in a table with the total row turned on and different slicer selections.
What is the difference between ALL and REMOVEFILTERS in Power BI?
As CALCULATE modifiers they behave the same, removing filters from the named table or columns. REMOVEFILTERS is clearer about intent, while ALL can also return a table for use in iterators like RANKX or FILTER.
Does Uncodemy help with Power BI interview questions?
Yes. Uncodemy's Power BI Training Course in Noida (classroom and online) includes live projects, one-to-one mock interviews and resume reviews, so you practise Power BI interview questions with trainer feedback before the real interview. You can book a free demo class from this page to see how the sessions work.
SECTION 09Related reads
Classroom & online · Noida
Answer the DAX question most candidates fumble
Our Power BI Training Course teaches DAX filter context, time intelligence and ranking through hands-on reports, so percent-of-total questions become straightforward for you.
Talk to a counsellor for current fees- DAX filter context practice
- Slicer test routines
- Mock Power BI interviews
- Weekday & weekend batches