Sum is not presented correctly

[Lesny]

Hi,

Could you please help and explain what I'm doing wrong here?

I've got a two tables with relation based on column "Product Family". Let's assume they both looks:

 

when I'm building an overview I receive:

How to change this?

[danextian]

Why are your tables with the same structure separate?

[Lesny]

Hello. The whole structure is not the same. I simplified the table to show the problem.

[grazitti_sapna · Accepted solution]

Hi @Lesny,

The issue in your Power BI report can be due to a many-to-many relationship between Table 1 and Table 2, both of which are related on the Product Family column. Here's how to fix and address the problem step by step:

Possible Reason for the Incorrect Totals

1. You have a direct relationship between Product Family in Table 1 and Table 2.
2. When you add Sales and Costs to your matrix, Power BI does not properly aggregate the data for each combination because it does not know how to match Sales with Costs by row unless explicitly guided.
   This results in duplicating or inflating totals in your report.

Possible Fixes and Solutions

Option 1: Use a Bridge Table

1. Create a Bridge Table:
   • Extract a unique list of Product Family values from both Table 1 and Table 2.
   • Name this table Product Family Bridge.
     Example DAX to create the bridge table:

     ProductFamilyBridge = DISTINCT(
     
     UNION(
     
     SELECTCOLUMNS(Table1, "Product Family", Table1[Product Family]),
     
     SELECTCOLUMNS(Table2, "Product Family", Table2[Product Family])
     
     )
     
     )

2. Define Relationships:

   • Remove the direct relationship between Table 1 and Table 2.

   • Connect Product Family in Table 1 to Product Family in Product Family Bridge.

   • Connect Product Family in Table 2 to Product Family in Product Family Bridge.

3. Update Your Matrix:

   • Use the Product Family field from the Product Family Bridge table in your visual.

   • Add Sales from Table 1 and Costs from Table 2.
     This ensures Power BI aggregates the data correctly without duplication.

Option 2: Use a Composite Model with Measures
If you prefer to avoid creating a bridge table, you can handle the relationship via explicit measures:

1. Create a Measure for Sales:
   TotalSales = SUM(Table1[Sales])
   
   
2. Create a Measure for Costs:

   TotalCosts = SUM(Table2[Cost])

3. Use These Measures in Your Matrix:

   • Add Product Family from either table to your matrix.

   • Use TotalSales and TotalCosts as the values.
     Power BI will correctly compute totals without inflating them.

Option 3: Use the Country Field for Additional Granularity
If Country is important for your analysis, ensure relationships also consider Country:

• You can create a concatenated column in both tables:

  ProductCountryKey = 'Table'[Product Family] & "-" & 'Table'[Country]

• Use this concatenated column to establish a 1-to-1 relationship between Table 1 and Table 2.

I hope the provided multiple options works for you

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[Lesny]

Thank you. Solution 1 and 3 helped 🙂