Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Average Count of Active Employees by Time and Day

Hi! 

 

I'd like to be able to see a chart that counts the number of active employees by time of day, given the employee ID, punch in time, and punch out time (see screenshot below). I have data for multiple weeks, so what I would like to display is the average number of employees working at a given time for each day.

 

Thanks in advance for the help!  

 

 

Staff Data.PNGCapture.PNG

 

 

 

 

1 ACCEPTED SOLUTION

ahh, sorry about that.  As I said I had a few problems with the files and I was trying a few different ways to sort it out.  Try this.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

8 REPLIES 8

This is a non trivial problem but can be solved by writing measures that show running totals. It becomes a lot more complex if people can clock out after midnight. I suggest you will need to provide some sample datahat covers all scenarios for anyone to help you. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Thanks, here is the sample data:

 

https://1drv.ms/u/s!AkiwPIFoA01Eaw4ACPmY33l4FcA

 

 

the sample data doesn't look valid to me.  Note below that employee ID 1 is checking in and out multiple times on the same day.

 

image.png



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Hi Matt,

 

If you switch Employee ID from "Count" to "Don't Summarize", you should see the correct IDs. They are 5-digit numbers such as 10085.

 

Thanks! 

Ahh, sorry.  That is a bug in Desktop - when you add an ID, it counts rather than shows the number.  Not knowing the data I didn't notice it occur



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

I have had quite a few issues with the test file.  For what ever reason I cannot see the data in the data view.  There seem to be dates that are blank, but I am guessing.  Also the date formats are playing up between US and AU formats. Regardless, this may be close to what you are after.

 

The calendar table I created is at the minute level - it only needs to be at the granularity you need for reporting.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

It seems as though your file is incompatible with my version of PowerBI, although I have the most updated version that is available.

ahh, sorry about that.  As I said I had a few problems with the files and I was trying a few different ways to sort it out.  Try this.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.