Forum Discussion
How to create drill-down matrix hierarchy with pre-aggregated data without double-counting?
Super UserHi MayaBackovic,
You can achieve the same by using ISINSCOPE() function to aggregate heirarchy level data.
I've attached a sample.pbix for your reference.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
Hi, thank you for the ISINSCOPE solution — it works perfectly for the sample data structure you provided. However, my data structure is slightly different and I'm struggling to adapt it.
Your example has separate columns for each hierarchy level (Category, SubCategory, Brand, SKU). My source data comes pre-aggregated with a single PRODUCT column that contains all hierarchy levels mixed together, repeated across multiple periods:
PRODUCT | PERIOD | REGION | CHANNEL | Volume
Electronics | Q1 | Region 1 | All | 1050
Phones | Q1 | Region 1 | All | 1050
Apple | Q1 | Region 1 | All | 650
iPhone 13 | Q1 | Region 1 | All | 300
iPhone 14 | Q1 | Region 1 | All | 350
Samsung | Q1 | Region 1 | All | 400
Galaxy S23 | Q1 | Region 1 | All | 400
Electronics | Q2 | Region 1 | All | 1150
Phones | Q2 | Region 1 | All | 1150
Apple | Q2 | Region 1 | All | 700
iPhone 13 | Q2 | Region 1 | All | 350
iPhone 14 | Q2 | Region 1 | All | 350
Samsung | Q2 | Region 1 | All | 450
Galaxy S23 | Q2 | Region 1 | All | 450
Key point: every row already has its own pre-calculated value — parent rows (Electronics, Phones) are not derived by summing child rows. They are independent aggregates provided by the data source. This means I cannot use SUM to roll up values through the hierarchy.
To enable hierarchy display in Power BI, I created a separate mapping table with PRODUCT, LEVEL, and hierarchy columns (Category, SubCategory, Brand, SKU), joined to the fact table on PRODUCT. But when I use the hierarchy columns from the mapping table in Matrix Rows with your ISINSCOPE measure, child rows show blank values.
My current workaround: using Data[PRODUCT] in Rows with MAX(Data[Volume]) — this gives correct values for all rows but loses hierarchy expand/collapse and proper sort order.
Question 1: Is ISINSCOPE applicable to this single-PRODUCT-column structure, and if so, how should the mapping table and measure be structured?
Question 2: What is the recommended way to sort Data[PRODUCT] by a SORT_ORDER column from a related mapping table?
Thank you!