How to create drill-down matrix hierarchy with pre-aggregated data without double-counting?

[MayaBackovic]

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?

[v-nmadadi-msft]

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

[v-nmadadi-msft]

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.

[MayaBackovic]

"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?"

[grazitti_sapna]

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!

[MayaBackovic]

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!

[v-nmadadi-msft · Accepted solution]

Hi @MayaBackovic ,

In this solution, we are working with pre-aggregated data where each product level (Category, SubCategory, Brand, SKU) already has its own independent value, so we are not performing any aggregation like SUM. Instead, we introduced a separate mapping table that defines the product hierarchy and connects to the fact table through the PRODUCT column. This allows the hierarchy (Category -> SubCategory -> Brand -> SKU) to drive the filtering context in the matrix. Because each PRODUCT has only one value per context, we use a the existing valoume column to retrieve the values in the matrix. The mapping table ensures proper drill-down behavior and sorting, while the relationship ensures that selecting any hierarchy level correctly filters the underlying data.
Uploaded .pbix for reference.

I hope this information helps. Please do let us know if you have any further queries.
Thank you