Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to calculate employee's total learning time

Hi,

 

I have a table for the employee's learning and training details.

 

Column names are: Empolyee Name, Designation, Department, Training Name, start date, end date, Time (time spent on learning/training in minutes), Trainer (Yes - if an employee acts as trainer)

 

Employees log their various learning entries into the table on weekly basis.

 

Now I want to find which employee is more active in logging their learning entries (based on multiple rows and learning) and which employee has spent more time in learning and which employee spent more time in giving trainings to others using power BI query.

 

Thank you.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Suppose we have a table like this.

    Please try like:

    Number of learning entries = COUNTROWS('Table')
    Number of trainings provided to others = CALCULATE( COUNTROWS('Table'), 'Table'[Trainer] = "Yes")
    Total learning time = SUM('Table'[Time])
    Top 1 learning entries = 
VAR _top_emp = 
    TOPN(
        1,
        ALLSELECTED( 'Table'[Empolyee Name] ),
        [Number of learning entries]
    )
RETURN
    CALCULATE(
        MAX('Table'[Empolyee Name]),
        KEEPFILTERS(_top_emp)
    )
    Top 1 learning time = 
VAR _top_emp = 
    TOPN(
        1,
        ALLSELECTED( 'Table'[Empolyee Name] ),
        [Total learning time]
    )
RETURN
    CALCULATE(
        MAX('Table'[Empolyee Name]),
        KEEPFILTERS(_top_emp)
    )
    Top 1 trainings provided = 
VAR _top_emp = 
    TOPN(
        1,
        ALLSELECTED( 'Table'[Empolyee Name] ),
        [Number of trainings provided to others]
    )
RETURN
    CALCULATE(
        MAX('Table'[Empolyee Name]),
        KEEPFILTERS(_top_emp)
    )

    Result:

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Suppose we have a table like this.

    Please try like:

    Number of learning entries = COUNTROWS('Table')
    Number of trainings provided to others = CALCULATE( COUNTROWS('Table'), 'Table'[Trainer] = "Yes")
    Total learning time = SUM('Table'[Time])
    Top 1 learning entries = 
VAR _top_emp = 
    TOPN(
        1,
        ALLSELECTED( 'Table'[Empolyee Name] ),
        [Number of learning entries]
    )
RETURN
    CALCULATE(
        MAX('Table'[Empolyee Name]),
        KEEPFILTERS(_top_emp)
    )
    Top 1 learning time = 
VAR _top_emp = 
    TOPN(
        1,
        ALLSELECTED( 'Table'[Empolyee Name] ),
        [Total learning time]
    )
RETURN
    CALCULATE(
        MAX('Table'[Empolyee Name]),
        KEEPFILTERS(_top_emp)
    )
    Top 1 trainings provided = 
VAR _top_emp = 
    TOPN(
        1,
        ALLSELECTED( 'Table'[Empolyee Name] ),
        [Number of trainings provided to others]
    )
RETURN
    CALCULATE(
        MAX('Table'[Empolyee Name]),
        KEEPFILTERS(_top_emp)
    )

    Result:

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum