Filter context concept in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand how sales vary by product category and region, and how filters affect the sales calculations.

📊 Data: You have sales data including Date, Region, Product Category, and Sales Amount.

🎯 Deliverable: Create a Power BI report that shows total sales by product category and region, demonstrating how filter context changes the sales totals.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1000
2024-01-07	South	Electronics	1500
2024-01-10	North	Clothing	700
2024-01-12	East	Clothing	900
2024-01-15	South	Furniture	1200
2024-01-18	East	Furniture	1100
2024-01-20	North	Electronics	1300
2024-01-22	South	Clothing	800
2024-01-25	East	Electronics	1400
2024-01-28	North	Furniture	1000

1

Step 1: Load the sales data into Power BI and create a table visual with columns: Region, Product Category, and Sales Amount.

No formula needed; just load data and create table visual.

Expected Result

Table shows all sales records with their regions, product categories, and sales amounts.

2

Step 2: Create a measure to calculate total sales using DAX.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

Measure 'Total Sales' sums all sales amounts in the data.

3

Step 3: Add a matrix visual with Rows = Product Category, Columns = Region, Values = Total Sales measure.

Configure matrix visual fields accordingly.

Expected Result

Matrix shows total sales broken down by product category and region.

4

Step 4: Add slicers for Region and Product Category to the report page.

Add slicers using Region and Product Category columns.

Expected Result

User can filter the report by selecting specific regions or product categories.

5

Step 5: Explain filter context: When a user selects a region or product category in slicers, the Total Sales measure recalculates only for the filtered data.

No formula; explanation of filter context behavior.

Expected Result

Total Sales values update dynamically based on slicer selections, showing filtered sales totals.

6

Step 6: Create a measure to calculate total sales ignoring filters on Region.

Total Sales All Regions = CALCULATE([Total Sales], ALL('Sales'[Region]))

Expected Result

Measure shows total sales ignoring any Region filter but respects other filters.

7

Step 7: Add the 'Total Sales All Regions' measure to the matrix visual to compare filtered and unfiltered sales by region.

Add measure to matrix values.

Expected Result

Matrix shows two columns per region: filtered sales and sales ignoring region filter.

Final Result

Product Category | North  | South  | East   
-------------------------------------------
Electronics      | 2300   | 1500   | 1400   
Clothing         | 700    | 800    | 900    
Furniture        | 1000   | 1200   | 1100   

Filters Applied: Region = South

Total Sales updates to show only South region sales.

Additional Column: Total Sales All Regions shows sales ignoring Region filter.

✓Sales vary by product category and region, with Electronics having highest sales in North region.

✓Filter context changes the sales totals dynamically when slicers are used.

✓Using ALL function in DAX removes filter on Region, showing total sales across all regions regardless of slicer selection.

Bonus Challenge

Create a measure that calculates total sales only for the selected product category but ignores any region filter.

Show Hint

Use CALCULATE with ALL on Region column and keep the filter on Product Category from filter context.