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