Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Adding a column from a related table

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.  

Need Help
  • FarhanAhmed's avatar
    FarhanAhmed
    4 years ago

    Create a calculated column like below in Order Table.

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

     

4 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Create a calculated column like below in Order Table.

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

     

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    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
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        FWPBIDev
        Frequent Visitor
        1 year ago

        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.