Forum Discussion
Getting an Average Point In Time by Date and Individual
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.
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'
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.
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")