Look up and sum value from second table

[bchager6]

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.

 

[v-xicai]

Hi @bchager6 ,

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.

[samdthompson]

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]

Hi @bchager6 

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.

[bchager6]

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

[mwegener]

Hi @bchager6 ,

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.

[mwegener · Accepted solution]

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.