Adding a column from a related table

[Anonymous]

I have two tables with one-to-many relationship between them. One is an 'orders' table and the other is 'details' table.

Orders table have unique OrderIDs while the Details table can have non-unique OrderIDs. For example orderID 1234 in Table Orders has two matching rows in Table Details. I want to add the values in column 'Sales' of table Details and show that as "total sales" in the Orders table. Any ideas or suggestions on how to go about this? I am pretty new to PowerBI.  

[FarhanAhmed · Accepted solution]

Create a calculated column like below in Order Table.

_Total Sales = CALCULATE(SUM('Table (2)'[sales]))

[selimovd]

Hey @Anonymous ,

when they are connected you can get the value with a calculated column and the RELATED function.

This should work:

Sales = RELATED( Orders[Sales] )

Be aware that calculated columns are static. So the related value will be shown in the details table, but the value won't change if you do some slicing or change the context.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi, I saw this on another thread on here and tried it and it didn't work. I get an error saying that the tables aren't related even when I made sure that there was a relation between the two tables. I am guessing it does that cause of the one-to-many cardinality. Thanks for your response. 

[FWPBIDev]

No, Related will not work for the use case given, which clearly states summing values from the many-side to the one-side. Relatedtable or calculatetable should work for you instead. Related only works when trying to get values from the one-side to the many-side.