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.

Practice

1. What is the main purpose of a Decomposition tree visual in Power BI?

easy

A. To combine multiple unrelated datasets

B. To create a static summary table of data

C. To display data as a simple bar chart

D. To break down a measure into parts for detailed analysis

Solution

1. Step 1: Understand the function of a decomposition tree

   A decomposition tree helps explore a measure by breaking it down into smaller parts or categories.

2. Step 2: Compare options with this function

   Only To break down a measure into parts for detailed analysis describes breaking down a measure for detailed analysis, which matches the decomposition tree's purpose.

3. Final Answer:

   To break down a measure into parts for detailed analysis -> Option D

4. Quick Check:

   Decomposition tree = Break down measure [OK]

Hint: Think: decomposition means breaking down [OK]

Common Mistakes:

• Confusing decomposition tree with simple charts
• Thinking it creates static tables
• Assuming it merges unrelated data

2. Which of the following is the correct way to add a category to a Decomposition tree visual in Power BI?

easy

A. Drag a field to the 'Explain by' bucket

B. Drag a field to the 'Analyze' bucket

C. Drag a field to the 'Values' bucket

D. Drag a field to the 'Filters' pane

Solution

1. Step 1: Recall Decomposition tree buckets

   The 'Explain by' bucket is where you add categories to break down the measure in the decomposition tree.

2. Step 2: Match options to buckets

   Only Drag a field to the 'Explain by' bucket correctly identifies 'Explain by' as the place to add categories for decomposition.

3. Final Answer:

   Drag a field to the 'Explain by' bucket -> Option A

4. Quick Check:

   'Explain by' = categories in decomposition tree [OK]

Hint: Categories go in 'Explain by' bucket [OK]

Common Mistakes:

• Putting categories in 'Analyze' or 'Values' buckets
• Confusing 'Filters' pane with category placement
• Not knowing bucket names

3. Given a Decomposition tree analyzing total sales by Region and Product Category, what happens when you click on a Region node?

medium

A. It shows a tooltip with total sales for all Regions

B. It filters the entire report to only that Region

C. It drills down to show sales breakdown by Product Category within that Region

D. It resets the tree to show all Regions again

Solution

1. Step 1: Understand interaction in decomposition tree

   Clicking a node drills down to the next category level within that node's context.

2. Step 2: Apply to Region and Product Category

   Clicking a Region shows sales broken down by Product Category inside that Region.

3. Final Answer:

   It drills down to show sales breakdown by Product Category within that Region -> Option C

4. Quick Check:

   Click node = drill down next category [OK]

Hint: Click node to drill down next level [OK]

Common Mistakes:

• Thinking it filters whole report
• Assuming it resets the tree
• Confusing tooltip with drill down

4. You added a Decomposition tree visual but it shows no breakdown when clicking nodes. What is the most likely cause?

medium

A. No categories were added to the 'Explain by' field well

B. The measure used is not numeric

C. The visual is not connected to any data source

D. The report page is filtered to zero rows

Solution

1. Step 1: Check category setup in decomposition tree

   If no categories are in 'Explain by', the tree cannot break down the measure further.

2. Step 2: Evaluate other options

   While measure type and filters matter, the main reason for no breakdown is missing categories.

3. Final Answer:

   No categories were added to the 'Explain by' field well -> Option A

4. Quick Check:

   Missing 'Explain by' categories = no breakdown [OK]

Hint: Always add categories to 'Explain by' [OK]

Common Mistakes:

• Ignoring missing categories
• Blaming measure type first
• Not checking filters

5. You want to analyze sales by Region, then by Product Category, but only for the top 3 Regions by sales. How can you set this up using a Decomposition tree?

hard

A. Add Product Category to 'Explain by' and Region to 'Values' with a slicer for top 3

B. Add Region and Product Category to 'Explain by' and apply a Top N filter on Region by sales

C. Add Region to 'Analyze' and Product Category to 'Explain by' without filters

D. Add Region and Product Category to 'Explain by' and use a measure that sums only top 3 Regions

Solution

1. Step 1: Set categories in 'Explain by'

   Both Region and Product Category should be in 'Explain by' to allow drilling down.

2. Step 2: Apply Top N filter on Region by sales

   Use a Top N filter on Region to limit to top 3 Regions by sales before drilling down.

3. Final Answer:

   Add Region and Product Category to 'Explain by' and apply a Top N filter on Region by sales -> Option B

4. Quick Check:

   Top N filter + categories in 'Explain by' = correct setup [OK]

Hint: Use Top N filter on first category in 'Explain by' [OK]

Common Mistakes:

• Putting categories in wrong buckets
• Not applying Top N filter correctly
• Using measures instead of filters for top N