Material Hierarchy / Matrix and duplicate values

[Msampedro]

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.

 

[Msampedro]

@Poojara_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 level

    CALCULATE(SUM('Qty ACT QP1'[Quantity_in_Entry_Unit]))

)

[Poojara_D12]

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 level

    CALCULATE(

        SUM('Qty ACT QP1'[Quantity_in_Entry_Unit]),

        REMOVEFILTERS('CPU Tool'[Customer Part Number])  -- delete filter lower level

    )

)

[v-ssriganesh]

Hi @Msampedro,
Could you please provide sample data that clearly illustrates the issue you're experiencing?

• Include a small dataset that fully captures the issue (preferably in table format or as a downloadable file, not just a screenshot).
• Avoid including any sensitive or unrelated information.
• Also share the expected outcome based on the sample data you provide.

Need help preparing or uploading sample data? You can refer to this helpful guide:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

This will help us reproduce your scenario accurately and provide a precise solution.

Best regards,
Ganesh Singamshetty.

[Msampedro]

Hi @v-ssriganesh 

Please, see below the link to the PBI example. As you can see on the visuals, I have two Matrix. The left one has duplicates values for the Column Qty. The second one is what I am looking for (however, the duplicates are not shown due to a conditional format (Font white color) on the values and not totals. I am looking for something more conveneint.

Many thanks

https://drive.google.com/file/d/1O4p8VczeRqTAJhp-BX3RxS-sLhXNUQS0/view?usp=drive_link

[v-ssriganesh · Accepted solution]

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.

[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.