UNION of two tables with sum values

[ClaudioF]

Hello guys, 

here again just after breacking my head with this problem..

Look ate the cod bellow, i am trying to bring the unique values of the table DB_ORDERS and sum them with the found values of the table DB_BAL, in the DB_ORDERS for example i have the values in the column cod: 111, 222, 333, 333, 333, and the quantity 10, 20, 30, 30, 30, the the table DB_BAL i have the cod: 111, 222, 222, 333, 333 and the quantity: 1, 30, 50, 10, 20, the thing is, i wanna result a table with the unique values of both but in a way that i will only sum the values of the table DB_BAL, for all the repeating, but to the DB_ORDERS the value of quantity is allready my main value and i wanna a result like this: 111, 222, 333, quantity: 11, 100, 60. the code is: 

EstoqueEstimadoBa =

VAR TabelaDB_BAL =

    SUMMARIZE(

        DB_BAL,

        DB_BAL[D002_CODIGO_PRODUTO],

        "Quantidade", SUM(DB_BAL[PESO_CTO])

    )

VAR TabelaEstoq =

    SELECTCOLUMNS(

        DB_ORDERS,

        "D002_CODIGO_PRODUTO", DB_ORDERS[Produto],

        "Quantidade01", DB_ORDERS[Estoque Real]

    )

VAR TabelaUnida =

    UNION(TabelaDB_BAL, TabelaEstoq)

RETURN

    GROUPBY(

        TabelaUnida,

        [D002_CODIGO_PRODUTO],

        "QTde", SUMX(CURRENTGROUP(), [Quantidade])

    )

this by now is summing everything..

Thankyou a lot!

[v-venuppu]

Hi @ClaudioF ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @ClaudioF ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ClaudioF]

Thankyou for the reply, this code you gave is returning a error message: "No common join column detected. The join function 'NATURALLEFTOUTERJOIN' requires at least one common join column."

I have no idea of what is going on..

[v-venuppu]

Hi @ClaudioF ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FarhanJeelani for the prompt response.

"No common join column detected. The join function 'NATURALLEFTOUTERJOIN' requires at least one common join column."

means that the two tables (TabelaEstoq and TabelaDB_BAL) don’t share a column with the exact same name and data type—which is required for NATURALLEFTOUTERJOIN to work.

Here's few steps to fix it:

Make sure the column names match exactly in both tables. In your case, SELECTCOLUMNS is renaming the column as "D002_CODIGO_PRODUTO", but you also need to ensure the column name in TabelaDB_BAL matches exactly.

Here’s a corrected version:

EstoqueEstimadoBa =
VAR TabelaDB_BAL =
SELECTCOLUMNS(
SUMMARIZE(
DB_BAL,
DB_BAL[D002_CODIGO_PRODUTO],
"Quantidade", SUM(DB_BAL[PESO_CTO])
),
"D002_CODIGO_PRODUTO", [D002_CODIGO_PRODUTO],
"Quantidade", [Quantidade]
)

VAR TabelaEstoq =
SELECTCOLUMNS(
DB_ORDERS,
"D002_CODIGO_PRODUTO", DB_ORDERS[Produto],
"Quantidade01", DB_ORDERS[Estoque Real]
)

VAR TabelaFinal =
NATURALLEFTOUTERJOIN(TabelaEstoq, TabelaDB_BAL)

RETURN
ADDCOLUMNS(
TabelaFinal,
"QTde", [Quantidade01] + COALESCE([Quantidade], 0)
)

• SELECTCOLUMNS is used after SUMMARIZE to rename columns, ensuring consistency for the join.
• Now both tables have the common column D002_CODIGO_PRODUTO, which enables NATURALLEFTOUTERJOIN to work properly.
• COALESCE ensures that if there's no match in DB_BAL, it returns 0 instead of blank.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @ClaudioF ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[FarhanJeelani]

Hi @ClaudioF ,

You're close, but the issue lies in how you're handling the sum. Your UNION function is stacking both tables together, and then GROUPBY is summing both DB_ORDERS and DB_BAL quantities together. However, you want DB_ORDERS to stay as-is while summing DB_BAL.

Fix: Separate the logicAggregate DB_BAL to sum quantities per product.
Merge it with DB_ORDERS without summing DB_ORDERS again.
Use SUMX only for DB_BAL.

Modified DAX Code:

EstoqueEstimadoBa =
VAR TabelaDB_BAL =
SUMMARIZE(
DB_BAL,
DB_BAL[D002_CODIGO_PRODUTO],
"Quantidade", SUM(DB_BAL[PESO_CTO]) -- Sum only DB_BAL quantities
)

VAR TabelaEstoq =
SELECTCOLUMNS(
DB_ORDERS,
"D002_CODIGO_PRODUTO", DB_ORDERS[Produto],
"Quantidade01", DB_ORDERS[Estoque Real] -- Keep original DB_ORDERS quantity
)

VAR TabelaFinal =
NATURALLEFTOUTERJOIN(TabelaEstoq, TabelaDB_BAL) -- Join instead of UNION to avoid unnecessary summing

RETURN
ADDCOLUMNS(
TabelaFinal,
"QTde", [Quantidade01] + COALESCE([Quantidade], 0) -- Add summed DB_BAL quantity to DB_ORDERS
)

 Please mark this post as solution if it helps you. Appreciate Kudos.