Pivoting columns in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data showing product, month, and sales amount in rows.

Cell	Value
A1	Product
B1	Month
C1	Sales
A2	Apples
B2	Jan
C2	100
A3	Apples
B3	Feb
C3	120
A4	Oranges
B4	Jan
C4	80
A5	Oranges
B5	Feb
C5	90

Formula Trace

PivotedTable = SUMMARIZE( SalesData, SalesData[Product], "Jan Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan"), "Feb Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb") )

Step 1: SUMMARIZE(SalesData, SalesData[Product], "Jan Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan"), "Feb Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb"))

Step 2: Calculate Jan Sales for Apples: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan") where Product = Apples

Step 3: Calculate Feb Sales for Apples: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb") where Product = Apples

Step 4: Calculate Jan Sales for Oranges: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan") where Product = Oranges

Step 5: Calculate Feb Sales for Oranges: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb") where Product = Oranges

Step 6: Final Pivoted Table

Cell Reference Map

    A       B       C
1 |Product| Month | Sales |
2 |Apples | Jan   | 100   |
3 |Apples | Feb   | 120   |
4 |Oranges| Jan   | 80    |
5 |Oranges| Feb   | 90    |

References: Product (A2:A5), Month (B2:B5), Sales (C2:C5)

The formula uses columns Product, Month, and Sales from the SalesData table.

Result

    A        B         C
1 |Product | Jan Sales | Feb Sales |
2 |Apples  | 100       | 120       |
3 |Oranges | 80        | 90        |

The pivoted table shows each product with separate columns for January and February sales.

Sheet Trace Quiz - 3 Questions

What does the CALCULATE function do in this formula?

A Sorts the sales data alphabetically

B Filters sales by month and sums them

C Duplicates the sales data

D Deletes sales data for other months

Key Result

SUMMARIZE groups rows by a column and uses CALCULATE with filters to pivot data into new columns.