CALCULATE function introduction in Power BI - Cell-by-Cell Formula Trace

Sample Data

This table shows sales amounts for different products and their regions.

Cell	Value
A1	Product
A2	Apple
A3	Banana
A4	Cherry
B1	Sales
B2	100
B3	150
B4	200
C1	Region
C2	North
C3	South
C4	North

Formula Trace

TotalSalesNorth = CALCULATE(SUM(Sales), Region = "North")

Step 1: SUM(Sales)

Step 2: Region = "North" filter applied

Step 3: SUM(Sales) with filter Region = "North"

Step 4: CALCULATE(SUM(Sales), Region = "North")

Cell Reference Map

    A       B       C
1 Product  Sales  Region
2 Apple     100    North
3 Banana    150    South
4 Cherry    200    North

Formula uses Sales (B2:B4) and Region (C2:C4) columns.

The formula uses the Sales column for summing and filters rows based on the Region column.

Result

    A               B       C
1 Product      Sales  Region
2 Apple         100    North
3 Banana        150    South
4 Cherry        200    North
5
6 TotalSalesNorth: 300

The result of the CALCULATE formula is 300, which is the sum of sales for products in the North region.

Sheet Trace Quiz - 3 Questions

What does the CALCULATE function do in this formula?

A It multiplies sales by region

B It sums all sales without any filter

C It changes the filter context to only include North region

D It counts the number of products

Key Result

CALCULATE(expression, filter) changes filter context before evaluating expression