Need to Filter across whole model and divide by a count

[Anonymous]

Hi, 

I'm fairly new to Power BI and am struggling with a calculated column. I have attached a picture of my model below. My MergedTable contains logged expecteds and actuals for time spent on projects. The Team Leave table contains data about when members are on holiday, study etc. and my Leave Mappings contains more info the main one being whether the employee is actually working or not (Team Leave table has data regarding conferences, away days etc.). 

I am trying to create a column in the MergedTable called Expected Time that will multiply the Time column by the count of how many days an employee is Working in a week from the Team Leave table divided by 5. So every employee has 5 entries a week in the Team Leave table with 1 Week_Ending value. If they have a code which means they are not working for 2 days that week (from Leave Mappings) then I want to multiply my Time column in MergedTable by 3/5 to give my new column. 

Any help would be appreciated including alternative methods.

Thanks,

[ibarrau]

Hi. Before answering please consider reading about data modeling to learn about star schema and good practise to avoid things like bidirectional filter.

If you want to add a column in a table related to another one, you have to consider first if the relations for each row is one or more in order to understand if an aggregation is needed. After reading your example I think you need an aggregation. Supose you need to count rows, it should look like this:

NewColumn =
COUNTX ( 
    RELATEDTABLE(TableToCount) ,
    TableToCount[Column]
)

This will help you to add in MergedTable rows related from TeamLeave or you can do it in opposite way.

Hope this helps.

Regards,

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