CALCULATE function introduction 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 but only for the 'East' region. They want to understand how sales perform in that specific area.

📊 Data: You have a sales table with columns: Date, Region, Product, Quantity, and SalesAmount.

🎯 Deliverable: Create a measure using the CALCULATE function to show total sales for the East region and build a card visualization displaying this value.

Sample Data

Date	Region	Product	Quantity	SalesAmount
2024-01-01	East	Apples	10	100
2024-01-02	West	Oranges	5	50
2024-01-03	East	Bananas	7	70
2024-01-04	North	Apples	3	30
2024-01-05	East	Oranges	8	80
2024-01-06	South	Bananas	6	60
2024-01-07	West	Apples	4	40
2024-01-08	East	Bananas	9	90

1

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

Total Sales East = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] = "East")

Expected Result

A measure named 'Total Sales East' that sums SalesAmount only for rows where Region is East.

2

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

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

Expected Result

The Card shows the total sales amount for the East region, which is 340.

3

Step 3: Verify the calculation by summing SalesAmount for East region in the sample data.

100 + 70 + 80 + 90 = 340

Expected Result

The Card visualization displays 340, confirming the measure works correctly.

Final Result

-------------------------
|      Total Sales East  |
|          340           |
-------------------------

✓The CALCULATE function filters data to only include the East region.

✓Total sales for the East region is 340 based on the sample data.

✓This measure can be reused in other visuals to analyze East region sales.

Bonus Challenge

Create a measure that calculates total sales for the East region but only for the product 'Bananas'.

Show Hint

Use CALCULATE with two filter conditions: Sales[Region] = "East" and Sales[Product] = "Bananas".