Forum Discussion

Msampedro's avatar
Msampedro
Helper 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.

Msampedro's avatar
Msampedro
Helper I
10 months ago

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's avatar
v-ssriganesh
Community Support
9 months ago

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's avatar
    Msampedro
    Helper I
    9 months ago

    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's avatar
      v-ssriganesh
      Community Support
      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.