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 ,

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.