Why advanced visuals reveal deeper insights in Power BI - Formula Trace Breakdown

Sample Data

Sales data for three products across different 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

SUMX(FILTER(SalesData, SalesData[Region] = "North"), SalesData[Sales])

Step 1: FILTER(SalesData, SalesData[Region] = "North")

Step 2: SUMX([{Product: Apples, Sales: 100, Region: North}], Sales)

Cell Reference Map

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

Arrow: Region filter points to C2:C4, Sales sum points to B2:B4

The formula filters the Region column (C2:C4) to 'North' and sums the Sales column (B2:B4) for those filtered rows.

Result

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

Result Cell D1: 100

The final result is 100, which is the total sales for the North region only.

Sheet Trace Quiz - 3 Questions

What does the FILTER function do in this formula?

A Selects rows where Region is North

B Adds all sales values

C Changes sales values to zero

D Sorts the data by product

Key Result

SUMX(FILTER(Table, Condition), Column) sums values in a filtered subset of data.