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.

    Test (1).pbix198 KB
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.

Msampedro's avatar
Msampedro
Helper I
1 year ago

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

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

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    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.