Why interactivity enables exploration in Power BI - Formula Trace Breakdown

Sample Data

This table shows sales numbers for different products across regions.

Cell	Value
A1	Product
A2	Apples
A3	Bananas
A4	Cherries
B1	Sales
B2	100
B3	150
B4	120
C1	Region
C2	North
C3	South
C4	East

Formula Trace

CALCULATE(SUM(Sales[Sales]), Region[Region] = "North")

Step 1: SUM(Sales[Sales])

Step 2: Region[Region] = "North" filter applied

Step 3: CALCULATE(SUM(Sales[Sales]), Region[Region] = "North")

Cell Reference Map

    A        B        C
1 |Product | Sales  | Region
2 |Apples  | 100    | North
3 |Bananas | 150    | South
4 |Cherries| 120    | East

Formula references Sales column B2:B4 and Region column C2:C4

The formula uses the Sales values from column B and filters rows based on Region in column C.

Result

    A        B        C        D
1 |Product | Sales  | Region | Filtered Sales
2 |Apples  | 100    | North  | 100
3 |Bananas | 150    | South  | 
4 |Cherries| 120    | East   | 

Cell D2 shows the result of the filtered sum for Region 'North'.

The final result shows that only sales for the 'North' region are summed, resulting in 100.

Sheet Trace Quiz - 3 Questions

What does the CALCULATE function do in this formula?

A It sums all sales without filtering

B It sums sales only for the North region

C It filters sales for the South region

D It multiplies sales by region

Key Result

CALCULATE applies filters to modify the context before aggregating data.