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

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

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