Decomposition tree in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the factors driving total sales in the last quarter. They want an interactive report to explore sales by region, product category, and sales channel.

📊 Data: You have sales data including Date, Region, Product Category, Sales Channel, and Sales Amount for the last quarter.

🎯 Deliverable: Create a Power BI report with a decomposition tree visual that breaks down total sales by Region, then Product Category, then Sales Channel.

Sample Data

Date	Region	Product Category	Sales Channel	Sales Amount
2024-03-01	North	Electronics	Online	1200
2024-03-05	South	Clothing	Retail	800
2024-03-10	East	Electronics	Retail	950
2024-03-15	West	Home Goods	Online	700
2024-03-20	North	Clothing	Retail	650
2024-03-25	South	Home Goods	Online	400
2024-03-30	East	Clothing	Online	500
2024-04-05	West	Electronics	Retail	1100
2024-04-10	North	Home Goods	Retail	900
2024-04-15	South	Electronics	Online	1300
2024-04-20	East	Home Goods	Retail	750
2024-04-25	West	Clothing	Online	600

1

Step 1: Load the sales data into Power BI Desktop.

Use 'Get Data' > 'Excel' or 'CSV' to import the sales data table.

Expected Result

Sales data table appears in Power BI with columns Date, Region, Product Category, Sales Channel, Sales Amount.

2

Step 2: Create a new measure to calculate Total Sales.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

A measure named 'Total Sales' that sums all sales amounts.

3

Step 3: Insert a Decomposition Tree visual into the report canvas.

From Visualizations pane, select the Decomposition Tree icon.

Expected Result

An empty decomposition tree visual appears on the report.

4

Step 4: Add 'Total Sales' measure to the 'Analyze' field of the decomposition tree.

Drag 'Total Sales' measure into the Analyze field well.

Expected Result

The decomposition tree shows total sales as the root value.

5

Step 5: Add 'Region', 'Product Category', and 'Sales Channel' fields to the 'Explain by' field well in that order.

Drag 'Region', then 'Product Category', then 'Sales Channel' into Explain by field well.

Expected Result

The decomposition tree allows drilling down from Region to Product Category to Sales Channel.

6

Step 6: Interact with the decomposition tree by expanding nodes to explore sales breakdown.

Click the '+' icons next to Regions to see Product Categories, then Sales Channels.

Expected Result

You can see how sales are distributed by region, category, and channel.

Final Result

Total Sales: $9,850
├─ North: $2,750
│  ├─ Electronics
│  │  └─ Online: $1,200
│  ├─ Clothing
│  │  └─ Retail: $650
│  └─ Home Goods
│     └─ Retail: $900
├─ South: $2,500
│  ├─ Clothing
│  │  └─ Retail: $800
│  ├─ Home Goods
│  │  └─ Online: $400
│  └─ Electronics
│     └─ Online: $1,300
├─ East: $2,200
│  ├─ Electronics
│  │  └─ Retail: $950
│  ├─ Clothing
│  │  └─ Online: $500
│  └─ Home Goods
│     └─ Retail: $750
└─ West: $2,400
   ├─ Home Goods
   │  └─ Online: $700
   ├─ Electronics
   │  └─ Retail: $1,100
   └─ Clothing
      └─ Online: $600

✓North region has the highest total sales driven mainly by Electronics online and Home Goods retail.

✓South region sales are strong in Electronics online and Clothing retail.

✓East region sales are balanced across Electronics retail, Clothing online, and Home Goods retail.

✓West region sales are led by Electronics retail and Home Goods online.

Bonus Challenge

Add a slicer to filter the decomposition tree by month to analyze sales trends over time.

Show Hint

Use the Date column to create a slicer with Month or Quarter hierarchy and connect it to the decomposition tree visual.