ALL function for removing filters in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the total sales amount for each product category, ignoring any filters applied on the product subcategory.

📊 Data: You have sales data with columns: Date, Product Category, Product Subcategory, Sales Amount.

🎯 Deliverable: Create a report showing total sales by product category that ignores filters on product subcategory.

Sample Data

Date	Product Category	Product Subcategory	Sales Amount
2024-01-01	Electronics	Phones	500
2024-01-02	Electronics	Computers	700
2024-01-03	Furniture	Chairs	300
2024-01-04	Furniture	Tables	400
2024-01-05	Clothing	Shirts	200
2024-01-06	Clothing	Pants	250
2024-01-07	Electronics	Phones	600
2024-01-08	Furniture	Chairs	350

1

Step 1: Create a new measure to calculate total sales ignoring filters on Product Subcategory.

Total Sales by Category = CALCULATE(SUM('Sales'[Sales Amount]), ALL('Sales'[Product Subcategory]))

Expected Result

This measure sums Sales Amount for each Product Category ignoring any filter on Product Subcategory.

2

Step 2: Create a table visual in Power BI with Product Category in rows and the new measure 'Total Sales by Category' as values.

Table visual configuration: Rows = 'Sales'[Product Category], Values = [Total Sales by Category]

Expected Result

The table shows total sales per product category ignoring any subcategory filters.

3

Step 3: Apply a slicer or filter on Product Subcategory to test that the total sales by category does not change.

Add slicer on 'Sales'[Product Subcategory] and select any subcategory.

Expected Result

The total sales by category remains the same regardless of the subcategory selected.

Final Result

Product Category | Total Sales by Category
-----------------|------------------------
Electronics      | 1800
Furniture        | 1050
Clothing         | 450

✓Total sales by product category sums all subcategories even when a subcategory filter is applied.

✓The ALL function removes filters on Product Subcategory, allowing total sales to be consistent.

✓This helps managers see overall category performance without subcategory filter impact.

Bonus Challenge

Modify the measure to ignore filters on both Product Subcategory and Date columns.

Show Hint

Use ALL with multiple columns: ALL('Sales'[Product Subcategory], 'Sales'[Date]) inside CALCULATE.