Using data from one table in a calculation in another table

Hi Greenterer ,

The problem comes from having a many-many relationship and also not know what month you want to work out tasks per month.

One way to fix the problem is to create a third table called "Users" which will contain just Joe's name. I am assuming there will be Bob, Alex and many more users.

Then Users will have a 1-to-many relationship with Table 1 and Table 2 based on Name.

Then you can add the following measure in Users:

Average Tasks per day worked =

VAR days_worked = SUM( 'Table 2'[Days Worked] )

VAR total_tasks = COUNTA( 'Table 1'[Name] )

RETURN

DIVIDE( total_tasks, days_worked )

 

If you just display this on a table with Joe's name and the measure it will give you 0.08.

 

Joe's done 3 tasks in 2 months when he should have worked 36 days. 3/36 = 0.08

 

If you then put the Year/Month field as well in the table it will show you what you want to see.

 

 

Hope this helps.

 

If this answer helped, please mark it as the correct one and a thumbs up would be great 🙂

 

Boyan