How to filter sum function within Tablix using value within row

[gblanks]

Im having trouble with power BI Report Builder for somthing that feels like it could be pretty simple but isnt and it may be because i dont understand how to refrence values that are in diffrent scopes. Basically i have 2 tables, one is a table that just has jobs listed on it(So like Project Manager, Anylist, that kind of thing) and another that lists hours worked each week by diffrent people, and it includes a column that refrences what position they are, so it would be a one to many relationship between Job DataSet and Worked DataSet on their column. I have a tablix that is using the Jobs DataSet and what i want to do is have a column that sums up all of the hours worked, but filter it so that it only sums up the ones who their Job column is equal to the Job value of the row on the table, but i just cant get it to work. I can get it to only sum up ones whos Job column matches a static string, and a perameter, but i just cant for the life of me get it to use values within the row itself. Anyone have any help? Thanks!

[Anonymous · Accepted solution]

Hi @gblanks ,

Based on your description, try the following steps:

• Create a Lookup Function: Use the Lookup function to match the job value in the current row with the job value in the Worked DataSet. The Lookup function can be used to retrieve a value from another dataset based on a matching field.
• Sum the Hours: Use the Sum function in combination with the Lookup function to sum the hours worked for the matching job.

Like below formula:

=Sum(Lookup(Fields!Job.Value, Fields!Job.Value, Fields!HoursWorked.Value, "WorkedDataSet"))

Best Regards,
Adamk Kong

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