Add amount from other table to calculated column - Need Help in DAX

[azaterol]

Hello everyone,

I have given two tables. Invoice table and an order table.

These two tables are linked to each other via the invoice number. There is therefore a 1:n relationship.

I would like to add an amount X, which can be found in the invoice table, to the result of the calculated column. Here as an example:

Initial situation

Invoice - Table

DocNr		Amount X
123		$ 100

Article order - Table

DocNr		Article		Article amount		Article price		Calculated column
123		001		1		$ 3		$ 3
123		002		1		$ 2		$ 2
123		003		3		$ 5		$ 15

After correct DAX this should be shown in the Power BI Table Visual:

DocNr		Name of orderer		Calculated column with Amount X
123		John Doe		$ 120

My DAX:

Calculated column with Amount X= Article order [Article amount] * Article order[Article price]​

Hope you can help me here out with my DAX.

[Ashish_Mathur · Accepted solution]

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.

[123abc]

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