Calculating an average across a data table and a calendar table

[Anonymous]

I'm having some issues putting this down in DAX formula, I have a table that has number of hours worked by people and I want to have a basic average formula of these hours across all the calendar table data I have, except for saturdays and sundays. The table looks something like this:

ID Day Hours

33	31/02/2020	5
33	31/02/2020	3

And then the result would be an average of these hours across all those days in the calendar table where there's respective data for (aside weekend). Can anyone help me out with a way to approach this?

[Anonymous · Accepted solution]

I have managed to resolve this one at the end, I used this formula: 

var Employees = ADDCOLUMNS(Employees, "Daily Average Hours", CALCULATE(DIVIDE(SUM(Employees_Presence[Hours]), DISTINCTCOUNT(Employees_Presence[Date]))))
return

AVERAGEX(Employees, [Daily Average Hours])

Goes to create a table where every row has the average between the sum of the hours worked and the days they worked. Then, I take that table and recall the column I created above with AVERAGEX to obtain the average of the average hours for each.

[amitchandak]

@Anonymous , try a measure like 

calculate(average(Table[Hours]), filter(Table, weekday(Table[Date],2) <6 ) )

[Anonymous]

Thank you, this seems to work fine. I think I just need to find a way to address employees who are not working there anymore, or the average will be brought down, but this is in another table. Maybe I can do a SELECT COLUMNS variable before and use that as a table

[amitchandak]

@Anonymous , depends on how data is stored. Refer current employee of this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

I have merged the column which tells me whether employees are dismissed or not. So right now it would look like the same as you mentioned but with that filter in addition. I can't make it however, because it says AVERAGEX can't work with Boolean types.

CALCULATE(AVERAGE(Employees, Employees[Dismissed] <> TRUE()), FILTER('Calendar', WEEKDAY('Calendar'[Date],2) <6))