Cross-filtering between visuals in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data for products with their categories and sales amounts.

Cell	Value
A1	Product
B1	Category
C1	Sales
A2	Apple
B2	Fruit
C2	100
A3	Banana
B3	Fruit
C3	150
A4	Carrot
B4	Vegetable
C4	80
A5	Broccoli
B5	Vegetable
C5	120

Formula Trace

Total Sales = SUM(Sales[Sales])

Step 1: SUM(Sales[Sales])

Cell Reference Map

   A         B           C
1 Product   Category    Sales
2 Apple     Fruit       100
3 Banana    Fruit       150
4 Carrot    Vegetable    80
5 Broccoli  Vegetable   120

Visual 1: Bar chart of Sales by Category
Visual 2: Table of Products

Cross-filtering: Selecting 'Fruit' in Visual 1 filters Visual 2 to show only Apple and Banana.

The formula references the Sales column (C2:C5) to calculate total sales. The visuals use Category and Product columns to cross-filter each other.

Result

Visual 1: Sales by Category (Bar Chart)
Fruit       |█████████████████ 250
Vegetable  |██████████ 200

Visual 2: Product Table (Filtered by 'Fruit')
Product   Category   Sales
Apple     Fruit      100
Banana    Fruit      150

When the user clicks the 'Fruit' bar in Visual 1, Visual 2 updates to show only products in the Fruit category, demonstrating cross-filtering.

Sheet Trace Quiz - 3 Questions

What is the total sales value calculated by the formula?

A 450

B 250

C 200

D 100

Key Result

SUM aggregates values from a specified column to calculate totals.