Getting an Average Point In Time by Date and Individual

[DorienM]

Hi All,

I have a table with two important columns, the first column is someone's name, the second is a date/time stamp. This table is a record of every single time they scanned their badge to either enter the building, or to move around in access controled areas within the building.

Management wants to use this data to produce answers to the following two questions:

• For any given date, what is the average first scan time? (For example, on Mondays are people usually here later than on Tuesdays?)
• For any given individual, what is the average time of their first badge scan of the day? (What is the average time they begin work?)

This requires a measure that doesn't just summarize the date/time field as an average, but instead takes an average of the first date time by date and by individual.

How would you go about doing this? I had considered using a calculated column to easily store the first scan per day, but that introduces a bias based on the number of times someone scanned that day, if they scanned 5 times day 1, and 3 times day 2, then an average would have an unfair bias towards the first scan on day 1.

[RossEdwards]

The formula you have written is designed to give you the average start time of a range of dates.  I.e. its purpose is to come up with the total at the bottom of your table.

If you want the average start time, you need to use the formula that uses the employee in the VALUES function.

[DorienM]

I want two tables, average start time by date, and average start time by employee. The screenshot in my previous post was meant to show the table for average start time by date in which you can see only the min value is being returned, not the average of all start times that occured during that date.

I didn't depict the average start time by employee table but it is doing the same thing.

I have two seperate measures as you described, one for average start time by employee, and one for average start time by date, but both are still just returning the min value.

[RossEdwards]

The measure with the VALUES by date, will certainly work exactly as you described within a table that has 1 column for dates.  This is because, row by row, the date column is already doing the same job as VALUES function.  Once it gets to the total, thats when you get the correct value.

The measure with the VALUES for employee will work if you have used the correct employee column.  If that employee column is from another table compared to your table with badge scans, there will need to be the a 1 to many relationship between those two tables.  I'm expecting you either have an employee table where each employee has a badge number, or you have a badge history table which each badge number links back to a single employee.  Either way you should be able to create a basic table visual in Power BI where you put the employee as the first column and then their swipe times (with don't summarise) should be able to appear in the 2nd column to show that the modelling works.

[DorienM]

Thats it though, this isnt some complex data setup at all. Its all in a single table of badge scan logs.

Table Name: BadgeScans

Badge Number	Employee Name	Message	Location	Date/Time	Date Only (Added in at transform level)	Time Only (Added in at transform level)
01:00023	John Doe	Access Granted	Main Entry	4/1/2024 9:14:10 AM	4/1/2024	9:14:10 AM
01:00089	Mary Sue	Access Granted	SE Entrance	4/1/2024 10:03:38 AM	4/1/2024	10:03:38 AM

I can't post the whole dataset, but imagine about 10k more rows and John Doe, Mary Sue, and 100 other employees having multiple entries per day.

From this I have two measures written exactly as shown:

Average First Scan by Person = FORMAT(AVERAGEX(
    VALUES('BadgeScans'[Badge Number]),
    MIN('BadgeScans'[Time Only])
),"HH:MM:SS")

and

Average First Scan by Day = FORMAT(AVERAGEX(
    VALUES('BadgeScans'[Date Only],
    MIN('BadgeScans'[Time Only])
),"HH:MM:SS")

Then add two table visuals to the report. Table 1 has the fields 'Date Only' and 'Average First Scan by Day' on it. Table 2 has the fields 'Badge Number' and 'Average First Scan by Person'. Each row of the table only shows the MIN value by category, not the AVERAGE value by category.

Logically if John Doe came to work every day last week and scanned his badge at 5 doors each day, the would have 25 rows in Badge Scans. I want his average start time though, so I need the average of only 5 of the 25 values in the table, specifically the average of the 5 MIN values by Day.

[RossEdwards]

You've got the measures backwards compared to the tables.  Table 1 should be the fields 'Date Only' and 'Average First Scan by Person' on it. Table 2 should have the fields 'Badge Number' and 'Average First Scan by Date'

[DorienM]

Like this?

Because these are still only the min values. I know this because I am that highlighed badge on the right, I usually arrive around 6:45 AM and am generally the first one in the building that 6:18 is absolutely my earliest.

Most people don't get in until 8 or 9 so I know those are Min values in table 1 as well.

[RossEdwards]

Yes those tables are now correctly set up for the design of the measures.  If you are unsure if the answers are correct, grab 1 dates worth of data from the data table (copy table will do this).  Paste into Excel and do some manual checks.  I.e. find the minimums for each badge manually and see what the answer is.

If thats still not working, one additional trick to try is to create a base measure that handles the MIN function, call that measure from within your other two measures.  Sometimes the engine hates running aggregations directly.

What i mean is writing a new measure like

Minimum Badge Scans = MIN('BadgeScans'[Time Only])


Average Forst Scam by Day = FORMAT(AVERAGEX(
    VALUES('BadgeScans'[Date Only],
    [Minimum Badge Scans]
), "HH:MM:SS")

[RossEdwards]

First thing I would do is split the Date/Time column into its respective Date and Time components. You can keep the original column if you wish, but splitting these columns should make your life easier.  Time is a data type which is considered numeric, so you can run the standard Average functions over it, as well as Min.

To get Average first scan time we would use a formula like this:

Average First Scan by Person = AVERAGEX(
    VALUES('YourData'[Person],
    MIN('YourData'[ScanTime])
)

Average First Scan by Day = AVERAGEX(
    VALUES('YourData'[ScanDate],
    MIN('YourData'[ScanTime])
)

So with measures like this, you can apply a filter context to make them do what you want.  For example, if you select a single person and use the measure "Average First Scan by Day", you'll get a persons average start time.  You could then additionally filter the date for a range and see what the average was for a specific period.

Hopefully this concept makes sense to you and you can apply it in a way that makes sense to your data and model.

[DorienM]

@RossEdwards so I gave your suggestion a try, but despite the fact AVERAGEX is being used, the measure still seems to only return the first scan time in a given day as you can see here: