Forum Discussion
How to create drill-down matrix hierarchy with pre-aggregated data without double-counting?
I have a pre-aggregated dataset from an agency where each hierarchy level has its own row with pre-calculated values:
- Category Total = 158,244
- Sub-category = 45,000
- Brand = 11,804
- SKU = 350
The dataset also contains multiple periods (Q1, Q2, Q3, Q4, YTD, MAT) and regions — each combination of Product x Period x Region has its own row.
Leaf nodes (SKU level) sum up correctly to parent values. What is the best approach to enable drill-down hierarchy in Power BI matrix with this data structure, without double-counting?
6 Replies
Hi 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 | 450Key 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!
"Also, is a separate mapping table even the right approach for this data structure, or is there a better way to handle pre-aggregated data with a single PRODUCT column in Power BI?"
Hi MayaBackovic
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi MayaBackovic
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
