Need to Filter across whole model and divide by a count
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 TeamLeave 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 isWorking 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.
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: