Pivoting columns in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that shows total sales by product category for each month in a clear table format.

📊 Data: You have a sales dataset with columns: Date, Product Category, and Sales Amount. The data lists each sale as a separate row with the date and category.

🎯 Deliverable: Create a pivot table that shows months as columns, product categories as rows, and total sales as values.

Sample Data

Date	Product Category	Sales Amount
2024-01-05	Electronics	200
2024-01-15	Clothing	150
2024-02-10	Electronics	300
2024-02-20	Clothing	100
2024-03-05	Electronics	250
2024-03-15	Clothing	200
2024-01-25	Furniture	400
2024-02-28	Furniture	350
2024-03-20	Furniture	300

1

Step 1: Add a new column to extract the month name from the Date column.

Month = FORMAT('Sales'[Date], "MMM")

Expected Result

A new column 'Month' with values like 'Jan', 'Feb', 'Mar' for each row.

2

Step 2: Create a pivot table visual in Power BI.

Add 'Product Category' to Rows, 'Month' to Columns, and 'Sales Amount' to Values with aggregation SUM.

Expected Result

Pivot table showing product categories as rows, months as columns, and total sales summed in the cells.

3

Step 3: Format the pivot table for clarity.

Sort months in calendar order, format sales values as currency, and add a grand total row and column.

Expected Result

A clear, easy-to-read table with months in order Jan, Feb, Mar, sales formatted as currency, and totals shown.

Final Result

          | Jan    | Feb    | Mar    | Total   
------------------------------------------------
Electronics | $200   | $300   | $250   | $750    
Clothing    | $150   | $100   | $200   | $450    
Furniture   | $400   | $350   | $300   | $1,050  
------------------------------------------------
Total       | $750   | $750   | $750   | $2,250  

✓Electronics sales steadily increased from January to March.

✓Furniture had the highest total sales across the three months.

✓Clothing sales were lower but consistent.

Bonus Challenge

Create a line chart showing sales trends over the months for each product category.

Show Hint

Use the 'Month' column on the X-axis, 'Sales Amount' on the Y-axis, and 'Product Category' as the legend.