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.
Would dearly appreciate a set of fresh eyes.
I've been tasked with building a report that provides some insights as based on data from an access control db (sample data attached) The idea is to provide monthly reports that would be provided to the divisions that would highlights key stats.
These include:
1- List of staff per division that spent most time on site during the month (top 10)
2- List of staff per division that spent the least amount of time on site during the month (bottom 10)
My thought process was to create measures that would
1) Write earliest time per day per employee into new column
2) Write latest time per day per employee into another new column
3) Minus latest time with earliest time in a third new column and rank to get top and bottom lists.
Problem is, when I work with "CALCULATE" I am getting unexpected results, especially when trying to apply multiple filters to the measure for example:
DATE | TIME | TERMINAL | DIVISION | USER_ID |
Friday, 02 November 2018 | 06:32:34 | Staff Boom IN | Division 01 | Joe Soap |
Friday, 02 November 2018 | 06:33:07 | Boom Basement Ent | Division 01 | Joe Soap |
Friday, 02 November 2018 | 06:34:20 | Basement Lobby | Division 01 | Joe Soap |
Friday, 02 November 2018 | 06:35:33 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 07:03:45 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 08:18:20 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 08:43:59 | Visitor Boom IN | Division 02 | Jan Niemand |
Friday, 02 November 2018 | 08:44:30 | Staff Parking IN | Division 02 | Jan Niemand |
Friday, 02 November 2018 | 08:49:11 | Turnstile North 1 IN | Division 02 | Jan Niemand |
Friday, 02 November 2018 | 09:49:48 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 09:52:08 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 10:58:37 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 12:32:18 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 13:07:08 | 3rd Floor North | Division 01 | Joe Soap |
Friday, 02 November 2018 | 13:34:59 | Turnstile North 1 OUT | Division 02 | Jan Niemand |
Friday, 02 November 2018 | 13:38:49 | Staff Parking OUT | Division 02 | Jan Niemand |
Friday, 02 November 2018 | 13:39:09 | Staff Boom OUT | Division 02 | Jan Niemand |
Hi @jacosmit ,
What is your desired reuslt for your formula? Could you please post your desired result if possible?
Regards,
Daniel He
Hi Daniel
Something similar to the attached, per division.
MONTH | DIVISION | USER_ID | TIME ON SITE | RANKING |
Apr-19 | Division 01 | Joe Soap | 198:13:02 | 1 |
Apr-19 | Division 01 | Frank Noble | 198:11:00 | 2 |
Apr-19 | Division 01 | Stella Lange | 195:05:12 | 3 |
Apr-19 | Division 01 | Frank Niemand | 185:00:23 | 4 |
Apr-19 | Division 01 | Terry Crews | 156:00:15 | 5 |
Apr-19 | Division 01 | Tom Cruise | 154:00:14 | 6 |
Apr-19 | Division 01 | Rob Nelson | 150:10:15 | 7 |
Apr-19 | Division 01 | Ted Mosley | 149:02:45 | 8 |
Apr-19 | Division 01 | Sean Els | 148:00:15 | 9 |
Apr-19 | Division 01 | Tom Brady | 147:15:32 | 10 |
Hi @jacosmit ,
So could you mean the TIME ON SITE and RANKING column is your desired column? How the logic about the TIME ON SITE column?
Regards,
Daniel He
Hi Daniel
Yes, TIME ON SITE and RANKING would be what is required as output.
Logic with regards to the TIME ON SITE...... Conceptually I wanted to work out the earliest and latest time per person per day. Then subtract latest with earliest to get to the amount of time spent on site per day. Then add all the daily totals to get to the monthly total.
Any ideas?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |