cancel
Showing results for
Did you mean:
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

Data

So 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

5 REPLIES 5
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?

Proud to be a Datanaut!

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

## 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.

Proud to be a Datanaut!

Frequent Visitor

## Re: Average people on site by day of week

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

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?