CALCULATE with multiple 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 the 'East' region and for the product category 'Electronics' only.

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

🎯 Deliverable: Create a measure using CALCULATE with multiple filters to find total sales for 'East' region and 'Electronics' category, then build a card visualization showing this total.

Sample Data

Region	Product Category	Sales Amount	Date
East	Electronics	1000	2024-01-05
West	Electronics	1500	2024-01-06
East	Clothing	700	2024-01-07
North	Electronics	1200	2024-01-08
East	Electronics	800	2024-01-09
South	Clothing	600	2024-01-10
East	Electronics	400	2024-01-11
West	Clothing	900	2024-01-12

1

Step 1: Create a new measure in Power BI Desktop.

Total Sales East Electronics = CALCULATE(SUM('Sales'[Sales Amount]), 'Sales'[Region] = "East", 'Sales'[Product Category] = "Electronics")

Expected Result

The measure calculates the sum of Sales Amount where Region is East and Product Category is Electronics.

2

Step 2: Add a Card visualization to the report canvas.

Drag the measure 'Total Sales East Electronics' to the Card's Values field.

Expected Result

The Card shows the total sales amount for East region and Electronics category.

3

Step 3: Verify the total sales amount displayed matches the sum of sales for East region and Electronics category from the sample data.

Manually sum sales amounts: 1000 + 800 + 400 = 2200

Expected Result

The Card visualization displays 2200.

Final Result

-------------------------
| Total Sales East Electronics |
|           2200           |
-------------------------

✓Total sales for the East region and Electronics category is 2200.

✓Using CALCULATE with multiple filters allows precise control over which data is included in the calculation.

Bonus Challenge

Modify the measure to also filter sales only from the year 2024.

Show Hint

Add a filter condition using YEAR function on the Date column inside CALCULATE.