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.