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.

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

Hi Msampedro ,

Thanks for bringing this up in Microsoft Fabric Community.

In scenarios like this, where the matrix total differs from the sum of the individual row values, it's often due to how the DAX measure is being evaluated. The total in a matrix visual is not a simple addition of the rows but a separate evaluation over a different filter context.

 

A common way to resolve this is by using a SUMX(VALUES(...), ...) pattern in your measure, which makes it calculate the total by adding up the values from each row, so the total matches what's shown in the matrix.

 

Could you please share the DAX measure you're using along with a small sample dataset or mock PBIX file? That would help us verify the behavior and suggest the right fix.

 

Also, if this issue was already resolved through private message with the MFelix , it would be appreciated if you could share the final DAX or approach here (without any sensitive data) so others in the community can benefit from it.

 

Hope this helps. Please reach out for further assistance.

Thank you.

Thanks MFelix for your prompt response.