Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Look up and sum value from second table

Hello all...

Does anyone know if there's a way to look up and sum the hours per week in the second table below, by Resource #, and populate them in to a calculated column in the first table? The two tables are related by a third helper table containgin unique Resource #s.

 

 

 

 

Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Group your Table

    .. and merge them together

     

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

6 Replies

  • samdthompson's avatar
    samdthompson
    Memorable Member
    6 years ago

    Unpivoting in powerquery was a good start, now group by the resource ID, summing the Hrs/wk.

     

     

     

     

    // if this is a solution please mark as such

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi Anonymous 

     

    please use Power Query for these kinds of transformations.

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      mwegener I used Power Query to unpivot the data but am stuck at this point. Can you please be more specific?

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        6 years ago

        Hi Anonymous ,

         

        did you solve your problem?


        If I answered your question, please mark my post as solution, this will also help others.

        Please give Kudos for support.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You can create column in the first table Table1 like DAX below.

     

    Column1= CALCULATE(SUM(Table2[Hrs/Week]),FILTER(Table2,Table2[Resource #]=Table1[Resource ]))

     

    Best Regards,

    Amy

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.