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 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.08Material 1
            5,544,075.85Material 2
            2,958,767.06Material 3
            2,687,145.06Material 4
            2,201,734.03Material 5
            1,239,628.95Material 6
            1,190,072.46Material 7
-              325,140.31Material 8
         29,915,458.18Total

 

Regards,

Miguel

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.

10 Replies

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    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.

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

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

    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.