Forum Discussion
Helper VAdd amount from other table to calculated column - Need Help in DAX
Hi,
Create a single column table with all unique DocNr (Table name is Docs). Create a relationship (Many to One and Single) from the 2 tables to the Docs table. To your visual, drag DocNr from the Docs table. Write this measure
Total = sum(invoice[Invoice X]) + sum(Order[calculated column])
Hope this helps.
Community ChampionTo achieve the desired result in Power BI, you can create a calculated column that adds the Amount X from the Invoice table to the calculated column in the Article order table. Here's how you can do it using DAX:
Assuming your tables are named "Invoice" and "Article order," and they are related by the "DocNr" column, you can create a new calculated column in the "Article order" table like this:
DAX:
Calculated column with Amount X =
VAR CurrentDocNr = 'Article order'[DocNr]
RETURN
SUMX(
FILTER('Invoice', 'Invoice'[DocNr] = CurrentDocNr),
'Invoice'[Amount X]
) + 'Article order'[Article amount] * 'Article order'[Article price]
This DAX formula uses a variable (CurrentDocNr) to store the current DocNr from the "Article order" table row. Then, it uses the SUMX function to sum the "Amount X" values from the related rows in the "Invoice" table where the DocNr matches the CurrentDocNr. Finally, it adds the result of multiplying "Article amount" and "Article price" to the Amount X from the Invoice table.
Now, when you use this new calculated column in your Power BI visual, it should display the expected result:
SQL:
DocNr Name of orderer Calculated column with Amount X 123 John Doe $ 120
This DAX formula takes into account the 1:n relationship between the tables and calculates the correct result for each row in the "Article order" table based on the corresponding Amount X from the "Invoice" table.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.