Get weekly average of users

[mistahk]

Hi,

I'm needing to captue the weekly (weekday) average number of users who have badged into the office.

I've used this guide and while it works great, it only divides by the number of days there are data. If no one is in the office on Friday, it divides the data by 4 (Monday to Thursday) instead of the 5 (Monday to Friday) thus skewing the numbers.

https://blog.enterprisedna.co/copy-calculating-average-in-power-bi-isolating-weekday-or-weekend-resu...

Date	Location	User
June 12th, 2023	Washington	Jane Doe
June 12th, 2023	Washington	Billy Bob
June 13th, 2023	Washington	Jane Doe
June 13th, 2023	Washington	Billy Bob
June 13th, 2023	Washington	Victor Smith
June 13th, 2023	Washington	Sam Anderson
June 14th, 2023	Washington	Victor Smith
June 14th, 2023	Washington	Sam Anderson
June 15th, 2023	Washington	Victor Smith
June 15th, 2023	Washington	Sam Anderson

Current:

Weekday Average 10/4 = 2.5

Expected

Weekday Average 10/5 = 2.0

[Anonymous · Accepted solution]

Hi @mistahk ,

Please try this measure:

Weekday Average = var _days=CALCULATE(COUNT('Table'[Date]),ALLSELECTED('Table'))
return DIVIDE(_days,5)

If you also need to calculate by weekly grouping, try this:

Weekday Average Grouping = var _days=CALCULATE(COUNT('Table'[Date]),FILTER(ALLSELECTED('Table'),[Weeknum]=MAX('Table'[Weeknum])))
return DIVIDE(_days,5)

Best Regards,

Stephen Tao

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