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.

Msampedro's avatar
Msampedro
Icon for Helper I rankHelper 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
Icon for Community Support rankCommunity 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.