Relation between 3 tables

[Anonymous]

Hey everyone,

I have a file of three tables of mediocre quality. Therefore, it's difficult to get the data I want. 

In the table Employee, I have a column with the hours they work per week. As a result, I get 908. This is the sum of hours of the employees in the table Employee. But it doesn't add up. For example, when I select two week in the Calendar, it's still 908 instead of 1816. So, the relation doesn't work correctly, and I don't understand why. 

The relations between Calendar and Hours and between Hours and Employee work in both directions. The relation between Calendar and Hours is one-to-many and the relation between Employee and Hours is also one-to-many. 

[amitchandak]

@Anonymous , Try a measure like

AverageX(summarize(Hours, employee[Employee ID], Date[Year and Week No], "_1", sum(Hours[hours worked]) , "_2", Max(Hours Worked Per Week])), divide([_1],[_2]))

[Anonymous]

Hey @amitchandak ,

Thanks for your reply. Unfortunately, it doesn't work. So, I've made a dummy data set and a pbix-file. You can find the links below:

Excel-file:

https://docs.google.com/spreadsheets/d/1N4bAcrrYX9EnOpaGZHwUWaQYj5Pen2TU/edit?usp=sharing&ouid=11807...

PBIX-file

https://drive.google.com/file/d/1pWUBc-rNLpo7u2AR3DiZuWPGZjIunZbA/view?usp=sharing