cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CSourbutts Frequent Visitor
Frequent Visitor

Average people on site by day of week

Hi all,

 

I've access to an office entry system data set that records everytime an employee opens door in the builduing. From this I've been trying analyse the average number of unquie people, that have been in the office, by dayname of the week. Each person might have opened 100 doors on that day but I'm just interested in the fact 1 person was on site:

 

On an average :

Monday 37 people onsite

Tuesday 25 people onsite

ETC

 

Capture1.jpgData

Capture2.JPGSo far

I've been banging my head against this trying to adapt other solutions on the board without sucess. I'm specifically struggling to create a measure to convert the multiple door opens on a day to a single count of a person being on site (I could do this at the data level but don't want to lose the detail!).

 

For clarity:

 

To start with I'm simply looking for the total number of people who have visited the office in any one day. So as long as there is door open or close record for a date it indicates a person was on site and should be counted as 1.

 

At somepoint I may try to evolve the measure to show the maximum people onsite per day, accounting for people leaving, but not quite yet

 

THanks in advance!!

 

 

5 REPLIES 5
Super User
Super User

Re: Average people on site by day of week

So, could it be said that if the count of door opens for a person is odd, then they are onsite and if it is even they are offsite?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


CSourbutts Frequent Visitor
Frequent Visitor

Re: Average people on site by day of week

Apologies I was unclear.

 

To start with I'm simply looking for the total number of people who have visited the office in any one day. So as long as there is door open or close record for a date it indicates a person was on site and should be counted as 1.

 

At somepoint I may try to evolve the measure to show the maximum people onsite per day, accounting for people leaving, but not quite yet

Super User
Super User

Re: Average people on site by day of week

Could you just use something like:

 

Measure 10 = COUNTX(DISTINCT(Table[UserName]),[UserName])

That should give you a count of the distinct people on any give day, assuming that you put this in a visual with your Day.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


CSourbutts Frequent Visitor
Frequent Visitor

Re: Average people on site by day of week

Capture.JPG

 

H Greg - thanks for the help so far - when I chart this by day of week I get the total number of unique staff on site on any individual day (So there have been ~100 different people on site over all Mondays). What is needed is the average number of staff on site accross all days in the time period. So on Monday they are on average ~60 people on site..

CSourbutts Frequent Visitor
Frequent Visitor

Re: Average people on site by day of week

Hi there sorry to bump but does anyone have an idea of how I can get the average per day of week?