Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!!
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?
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
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.
Hi there sorry to bump but does anyone have an idea of how I can get the average per 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..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |