Calculate Total Hours of Training Completed by User

[Anonymous]

Hi,

using the linked data table, I am trying to build a measure that sums all hours training completed, divides it by the number of active users and converts the final result in days (1 day=7.33 hours) , giving me the possibility to put additional filters: date, division, organisation, type of training, type of user.

The relevant information are stored in 2 separate tables, in the same data model, the 2 tables can be linked by the USER ID field.

Here an example of expected results:

TOTAL NR OF ACTIVE USER IN OPERATIONS: 137

TOTAL HOURS OF TRAINING COMPLETED 3172

AVERAGE NR OF TRAINING DAYS PER PERSON =3.1

Thank you!

[v-yuta-msft · Accepted solution]

@Anonymous ,

Create three measures using dax below:

TOTAL NR OF ACTIVE USER IN OPERATIONS = CALCULATE(COUNT(Users[ID (User)]), FILTER(Users, Users[Division] = "OPERATIONS" && Users[Active (User)] = TRUE()))

TOTAL HOURS OF TRAINING COMPLETED = SUM('Completed Training'[Contact Hours (Item)])

AVERAGE NR OF TRAINING DAYS PER PERSON = [TOTAL HOURS OF TRAINING COMPLETED] / [TOTAL NR OF ACTIVE USER IN OPERATIONS] / 7.33

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.