Decomposition tree in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ visualization

intermediate

Understanding Decomposition Tree Node Expansion

You have a decomposition tree visual showing sales by region, then by product category, then by sales channel. If you expand the 'North America' node, which of the following best describes what you will see next?

A A breakdown of sales by product category within North America.

B A breakdown of sales by sales channel within North America.

C A total sales number for North America without further breakdown.

D A breakdown of sales by region within North America.

💡 Hint

Think about the order of levels in the decomposition tree.

Expanding a node in a decomposition tree shows the next level of breakdown. Since the order is region > product category > sales channel, expanding 'North America' shows product categories within that region.

❓ dax_lod_result

intermediate

DAX Measure for Decomposition Tree Node Value

Given a sales table with columns: Region, ProductCategory, SalesAmount, which DAX measure correctly calculates total sales for the current node in a decomposition tree?

Power BI

Total Sales = SUM(Sales[SalesAmount])

A Total Sales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

B Total Sales = SUM(Sales[SalesAmount])

C Total Sales = CALCULATE(SUM(Sales[SalesAmount]), VALUES(Sales[Region]))

D Total Sales = SUMX(Sales, Sales[SalesAmount])

💡 Hint

Consider how the decomposition tree filters data context automatically.

The simple SUM measure respects the current filter context set by the decomposition tree node. Using ALL removes filters, which is not desired.

❓ data_modeling

advanced

Modeling Data for Effective Decomposition Tree Use

You want to create a decomposition tree that breaks down sales by Region, then by Product Category, then by Customer Segment. Which data modeling approach is best to support this?

A Use only the Sales table with all columns and no relationships.

B Combine Region, Product Category, and Customer Segment into a single column in the Sales table.

C Create one dimension table combining Region and Product Category, and keep Customer Segment in Sales table.

D Create separate dimension tables for Region, Product Category, and Customer Segment, each related to the Sales fact table.

💡 Hint

Think about how hierarchies and filtering work best in Power BI.

Separate dimension tables allow clean hierarchies and filtering in decomposition trees. Combining columns or missing relationships reduces flexibility and performance.

🎯 Scenario

advanced

Using Decomposition Tree to Identify Sales Issues

Your decomposition tree shows total sales by Region > Product Category > Sales Channel. You notice a sudden drop in sales in the 'West' region. How can you use the decomposition tree to find the cause?

A Change the decomposition tree to show only total sales without breakdowns.

B Remove the 'West' region from the tree and focus on other regions.

C Expand the 'West' node to see which product categories have lower sales, then expand those categories by sales channel.

D Filter the entire report to only show the 'West' region without breakdowns.

💡 Hint

Use the tree's drill-down feature to explore details.

Expanding nodes lets you drill into where sales dropped, helping identify specific product categories or channels causing the issue.

🔧 Formula Fix

expert

Debugging Unexpected Behavior in Decomposition Tree Filters

You created a decomposition tree with levels: Country > City > Store. The measure 'Total Sales' is defined as Total Sales = SUM(Sales[Amount]). However, when you expand 'USA' to see cities, the sales numbers do not change and show the total for all countries. What is the most likely cause?

A The relationship between Sales and the Country dimension is inactive or missing, so filters do not apply.

B The Sales table has no data for cities, so the filter fails.

C The decomposition tree visual does not support multiple levels of breakdown.

D The measure uses SUM instead of CALCULATE, so it ignores filters.

💡 Hint

Check relationships and filter propagation in your data model.

If relationships are inactive or missing, filter context from the decomposition tree does not filter the Sales table, causing totals to remain unchanged.

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