Inconsistent Sum/Total in Matrix

[Msampedro]

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 the same total (30.9M), in reality the sum of ech amount = 29.1M

Just below the capture I have added the table in values so you can check the actual total by material is 29.1M - Any idea on how to solve it? Many thanks in advance!

14,419,175.08	Material 1
5,544,075.85	Material 2
2,958,767.06	Material 3
2,687,145.06	Material 4
2,201,734.03	Material 5
1,239,628.95	Material 6
1,190,072.46	Material 7
-              325,140.31	Material 8
29,915,458.18	Total

Regards,

Miguel

[v-veshwara-msft]

Hi @Msampedro ,

Just checking in to see if you query is resolved. If further assistance is needed, please reach out. Please share the DAX measure you're using along with a small sample dataset or mock PBIX file

Thank you.

[Msampedro]

Hi @v-veshwara-msft @MFelix ,

Thanks for your prompt response.

I am trying to share a mock PBIX file but the file type (.pibx) is not supported. Do you know how I can share the file with you?

Thanks,

Miguel

[MFelix]

You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[v-veshwara-msft]

Hi @Msampedro ,
Thanks for getting back.

You can upload the mock PBIX file to a cloud storage service like OneDrive, Dropbox, Google Drive, or WeTransfer, then share the link here. Just make sure the link has the right access permissions so others can view or download the file.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[Msampedro]

Hi @v-veshwara-msft @MFelix 

Thanks. Please see below the link:

https://drive.google.com/drive/folders/1-OWDXovMOrAsn0nwPt3nkxZv2ecHfaVF?usp=sharing

Regards,

Miguel

[v-veshwara-msft]

Hi @Msampedro ,

Thanks for sharing the .pbix file.

I’ve reviewed the report, and the stacked column chart shows a total of around 29.73M because it sums the individual values exactly as they appear for each material. The chart calculates totals based only on the data points that are directly plotted, so the total is simply the sum of the visible bars.

In the matrix visual, the total shows 30.91M because the matrix calculates totals differently. It doesn’t sum the individual rows shown in the matrix. Instead, it re-evaluates the measure in the total row context, which may include additional rows from the underlying data that are not visible in the breakdown.

Also, since the relationship between the mapping and net sales tables is many-to-many, a single material can match to multiple layer mappings. This causes the total row to consider more combinations than those reflected in the visible rows, resulting in a higher total that doesn’t align with the row-level values.

This is evident when filtering a single material mapped to multiple layers - the matrix shows only one value while the stacked chart sums both, leading to a mismatch.

Hope this helps. Please reach out for further assistance.

Thank you.

[Msampedro]

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 · Accepted solution]

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]

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.

[MFelix]

Hi @Msampedro ,

Can you please share what is the calculation you are using to get this values?

I believe this is a context based the sum of values that are dependent on other calculations may return incorrect results in aggregations at different levels depending on the aggregator column.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.