Aggregations for performance in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data with products, their categories, and sales amounts.

Cell	Value
A1	Product
B1	Category
C1	SalesAmount
A2	Chair
B2	Furniture
C2	150
A3	Table
B3	Furniture
C3	300
A4	Pen
B4	Stationery
C4	20
A5	Notebook
B5	Stationery
C5	50

Formula Trace

SUMX(SUMMARIZE(Sales, Sales[Category], "CategorySales", SUM(Sales[SalesAmount])), [CategorySales])

Step 1: SUMMARIZE(Sales, Sales[Category], "CategorySales", SUM(Sales[SalesAmount]))

Step 2: SUMX([{"Category":"Furniture","CategorySales":450},{"Category":"Stationery","CategorySales":70}], [CategorySales])

Cell Reference Map

    A          B           C
1 Product    Category   SalesAmount
2 Chair     Furniture      150
3 Table     Furniture      300
4 Pen       Stationery      20
5 Notebook  Stationery      50

Formula references Sales[Category] and Sales[SalesAmount] columns from rows 2 to 5.

The formula uses the Category and SalesAmount columns from the Sales table rows 2 to 5.

Result

    D
1 Total Sales
2 520

The final result shows total sales amount 520, calculated by summing sales grouped by category.

Sheet Trace Quiz - 3 Questions

What does the SUMMARIZE function do in this formula?

A Filters sales to only one category

B Groups sales by category and calculates total sales per category

C Adds all sales amounts without grouping

D Calculates average sales per product

Key Result

SUMX(SUMMARIZE(Table, GroupColumn, "Alias", SUM(ValueColumn)), [Alias]) aggregates data by group then sums.