Forum Discussion

Msampedro's avatar
Msampedro
Icon for Helper I rankHelper I
10 months ago
Solved

Material 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...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    9 months ago

    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's avatar
Poojara_D12
Icon for Super User rankSuper User
10 months ago

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.