Power BI Intermediate Lesson 2: % of Total With ALL, ALLEXCEPT and REMOVEFILTERS

📎 This article includes 3 downloadable practice files ↓

📘 Power BI Intermediate Course · Lesson 2 of 10

A share needs two numbers: the current one and a bigger one with some filters removed.

% of Total       = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Customers[Region] ) ) )
% of Grand Total = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Sales ) ) )
% of Category    = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( Products, Products[Category] ) ) )

Laptops are 73.4% of Computers sales: ALLEXCEPT removes the product filter but keeps the category.

Which remover?

• REMOVEFILTERS(column): clearest; removes filters from that column only. Slicers on other fields (e.g. FY) still apply.
• ALL(table): removes every filter on the table, slicers included. Use for a true grand total.
• ALLSELECTED(column): respects the user’s slicer but ignores the visual’s own grouping: “share of what’s selected”.

Practice

Import pbi-intermediate-model.xlsx into Power BI Desktop (free), create the relationships listed at the top of the .dax file, mark the Date table as a date table and add this lesson’s measures. Compare your card and table values with the lesson’s sheet in the expected-results file. Those numbers were worked out independently in Python from the same data, so a mismatch means a filter or relationship to check.