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 - long time lurker, first time poster, so apologies in advance if this is the incorrect sub:
I am hoping to summarize completed/scheduled event duration data with values from three tables.
table A - Events: Contains running registry of training events.
table B - Trainer Logs
table C - Trainer Capacity
The goal is relatively straightforward:
For each unique trainer ID + trainer name in table B/C, summarize:
Deducting the "Hours Booked or Completed" value from the hard-coded maximum allowable hours (160) that any one trainer has available in any one quarter should then allow me to arrive at a % of Quarterly Hours Used value, which would prove useful when answering the question of which trainers have the capacity remaining to book them with future events.
I contemplated bringing the trainer_id value (table B) into the events table (table A) via LOOKUPVALUE, but was unsure if there was a more elegant solution that virtualized this relationship.
Lastly, it's worth noting that an event can have multiple trainers assigned to it. In these instances, I'd like to avoid counting a single event with duration = 8 hours and >1 trainer assigned as 8 hours consumed by each assigned trainer, such that the duration of the event is split by the number of trainers assigned, i.e.,:
If duration = 8 hours && assigned trainer count = 1, then deduct 8 hours from trainer 1's capacity
If duration = 8 hours && assigned trainer count = 2, then deduct 4 hours from trainer 1's capacity and 4 from trainer 2's capacity
If duration = 8 hours && assigned trainer count = 3, then deduct 2.67 hours from t1, 2.67 hours from t2, 2.67 from t3
...etc.
Link to sample data below. Thanks
sample data
EDIT: Public link
ok. what is your question?
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |