Forum Discussion
Helper IMaterial Hierarchy / Matrix and duplicate values
Hi there,
Building a matrix based on two tables. Table1 is a material hierarchy, where several final materials belong to one raw material. Table2 I have Qty consumed by raw material. When doing a matrix with Raw materials and final materials in columns and Qty as values, I have duplicate values as you can see in the attached file. I have tried several DAX formulas but still not working. Many thanks.
Hello Msampedro,
Thank you for sharing sample data.I reproduced your scenario using your sample tables (Material Mapping and Pivot).
The duplicate Qty values occurred because each raw material maps to multiple final materials, causing the Qty to repeat at the detailed level.To fix this, I created a DAX measure:
Unique Qty := VAR _Raw = SELECTEDVALUE('Material Mapping'[Raw material]) RETURN IF( ISINSCOPE('Material Mapping'[Final Material]), BLANK(), CALCULATE(SUM('Pivot'[Qty]), 'Pivot'[Material] = _Raw) )This displays Qty only once per raw material and avoids duplicates under each final material.
The result matches your expected matrix view (without needing conditional formatting).
I’ve attached the .pbix file for your reference.
Best regards,
Ganesh Singamshetty.
7 Replies
MsampedroPoojara_D12 Many thanks for the quick response. I believe this is was I have tried to do with the following DAX Measure, but it is not working:
Total Qty by GMC =IF(ISINSCOPE('CPU Tool'[Customer Part Number]),BLANK(), -- Do not show Qty at Customer Part Number levelCALCULATE(SUM('Qty ACT QP1'[Quantity_in_Entry_Unit])))- Poojara_D12
Super User
Hi Msampedro
Your intention is correct, I think you're trying to prevent the quantity from repeating at the final-material level by returning a blank whenever the customer-part (child level) is in scope. However, the reason your DAX isn't working is that Power BI is still evaluating the raw-material total under the context of each child row, and simply blanking the visible cell doesn’t change the filter context being applied. Since the consumption table only has raw-level data, the SUM is still calculated once per raw material and then propagated to each child, causing repeated totals. The ISINSCOPE() logic only hides the result but doesn’t fix the evaluation grain. To solve this properly, you may need a measure that enforces evaluation only at the raw-material level using ISINSCOPE + REMOVEFILTERS or ALLSELECTED, or restructure your model so that consumption can be allocated or rolled up correctly. Hiding values alone won't prevent duplication because the aggregation still happens at the raw material level and is simply repeated down the hierarchy.
- Msampedro
Hi Poojara_D12
I have jus tried this one but nothing:
Total Qty by GMC =IF(ISINSCOPE('CPU Tool'[Customer Part Number]),BLANK(), -- Do not show Qty at Customer Part Number levelCALCULATE(SUM('Qty ACT QP1'[Quantity_in_Entry_Unit]),REMOVEFILTERS('CPU Tool'[Customer Part Number]) -- delete filter lower level))
- Poojara_D12
Hi Msampedro
The duplication in your matrix occurs because each raw material is linked to multiple final materials in your hierarchy table, but your consumption table stores quantities only at the raw-material level. When you place both raw and final materials in the matrix, Power BI repeats the raw-material quantity for each related final material, since there's no grain-level data for the final material to allocate the value uniquely. In other words, the model has a one-to-many relationship but your measure isn't filtering down to a lower level, so the same total appears multiple times. To correct this, you would either need a measure that returns the quantity only at the raw-material level and blanks at the child level, or restructure the data so consumption is attributed per final material. Without that, Power BI simply repeats the raw quantity for each child, creating the duplicated values you see.
