topic Calculating an average across a data table and a calendar table in DAX Commands and Tips

Calculating an average across a data table and a calendar table

Anonymous — Tue, 13 Sep 2022 06:32:15 GMT

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?

Re: Calculating an average across a data table and a calendar table

amitchandak — Tue, 13 Sep 2022 07:31:23 GMT

Anonymous , try a measure like

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

Re: Calculating an average across a data table and a calendar table

Anonymous — Tue, 13 Sep 2022 07:55:20 GMT

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

Re: Calculating an average across a data table and a calendar table

amitchandak — Tue, 13 Sep 2022 07:57:16 GMT

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

Re: Calculating an average across a data table and a calendar table

Anonymous — Tue, 13 Sep 2022 08:18:56 GMT

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))

Re: Calculating an average across a data table and a calendar table

Anonymous — Wed, 14 Sep 2022 06:35:47 GMT

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.