Forum Discussion

Msampedro's avatar
Msampedro
Helper I
1 year ago
Solved

Inconsistent Sum/Total in Matrix

Hello all,   Need help one more time.   I have the below inconsistent Sum/Total in a Matrix. As you can see Total Net sales = 30.9M. When adding in raws a material breakdown, despite of having th...
Need Help
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi Msampedro ,

    Thanks for getting back. Yes, the main issue was due to how the many-to-many relationship between the Net Sales and Mapping tables allows a single material to be associated with multiple layers. This causes the matrix visual to re-evaluate the total across all matching combinations, including ones not directly visible in the row-level breakdown - which results in a higher total.

     

    To fix this, here is a measure that explicitly sums Net Sales per unique Material–Layer combination from the Mapping table.

    Net Sales (Fixed Total) = 
SUMX(
    SUMMARIZE(
        'Mapping',
        'Mapping'[Material],
        'Mapping'[Layer]
    ),
    CALCULATE(SUM('Net sales'[Net Sales]))
)

    This forces the matrix to total exactly what is shown in the rows, avoiding the  context from the many-to-many relationship. With this change, the matrix total now becomes 29.73M.

     

    Hope this helps. Please reach out for further assistance.

    Please find the attached .pbix for reference.

    Thank you.

Msampedro's avatar
Msampedro
Helper I
1 year ago

Hi v-veshwara-msft 

Thanks for the analysis. 

Based on your comments the main issue would be the mapping, right?

 

Thanks,

Miguel

v-veshwara-msft's avatar
v-veshwara-msft
Community Support
1 year ago

Hi Msampedro ,

Thanks for getting back. Yes, the main issue was due to how the many-to-many relationship between the Net Sales and Mapping tables allows a single material to be associated with multiple layers. This causes the matrix visual to re-evaluate the total across all matching combinations, including ones not directly visible in the row-level breakdown - which results in a higher total.

 

To fix this, here is a measure that explicitly sums Net Sales per unique Material–Layer combination from the Mapping table.

Net Sales (Fixed Total) = 
SUMX(
    SUMMARIZE(
        'Mapping',
        'Mapping'[Material],
        'Mapping'[Layer]
    ),
    CALCULATE(SUM('Net sales'[Net Sales]))
)

This forces the matrix to total exactly what is shown in the rows, avoiding the  context from the many-to-many relationship. With this change, the matrix total now becomes 29.73M.

 

Hope this helps. Please reach out for further assistance.

Please find the attached .pbix for reference.

Thank you.