Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Connect a static table with a dynamic table

Hello everyone,

 

I have a static table with EmployeeID and their hours per week, which is based on their contract. Every employee's hours are registrered per day. I want to be able to compare the hours per week and their actual hours they've worked. And not only per week, but also per month or year. So the static table have to become more dynamic. Is this possible?  

 

 

Need Help
  • ERD's avatar
    ERD
    4 years ago

    Anonymous ,

    I've used 3 measures to get your result:

    H_worked = SUM ( 'T-fact'[Hours worked] )
    Hours_per_week = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( 'T-fact', 'T-fact'[EmployeeID], 'Date'[Week of Year] ),
        "@H/week",
            VAR currentEmpID = CALCULATE ( SELECTEDVALUE ( 'T-fact'[EmployeeID] ) )
            RETURN
                CALCULATE (
                    SUM ( 'T-contract'[Hours per week] ),
                    'T-contract'[EmployeeID] = currentEmpID
                )
    )
RETURN
    SUMX ( _t, [@H/week] )
    Utilisation Rate = DIVIDE ( [H_worked], [Hours_per_week] )

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

4 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous ,

    In general,

    • connect these 2 tables by EmployeeID column
    • create a separate Date table with all levels you need (week#, month, year, etc)
    • connect your dynamic table to the Date table by Date column
    • create measures and use them in visuals.

    If you want some particular measure, then, please, provide:

    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1 to 2.

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi ERD , 

       

      It would be great if you could provide me the measure. I created a sample set. You can find the link here:

      https://docs.google.com/spreadsheets/d/1Onv2UBeODDCfLOPiOOZUABiDrAtVyDkZ/edit?usp=sharing&ouid=118077823319969530855&rtpof=true&sd=true

       

      I added a date table by using the query in this link: 

      https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

       

      I hope it's possible to compare the Hours per week in 'employeeID' with Hours worked in 'sample'. I added a sheet, named 'result'. I hope this helps. If you have any question, please let me know. 

       

      I also tried the following measure:

       

      Hours employee by contract =
      SUMX(
      VALUES ( 'sample'[EmployeeID]),
      DATEDIFF ( MIN ( 'sample'[Date] ), MAX ( 'sample'[Date] ), WEEK )
      * MAX ( employeeID[Hours per week] )
      )
       
      Unfortunately, this measure didn't work, because it didn't responded well when I added a week filter. For example, when I selected 1 week, the hours returned was 0. 
      • ERD's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous ,

        I've used 3 measures to get your result:

        H_worked = SUM ( 'T-fact'[Hours worked] )
        Hours_per_week = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( 'T-fact', 'T-fact'[EmployeeID], 'Date'[Week of Year] ),
        "@H/week",
            VAR currentEmpID = CALCULATE ( SELECTEDVALUE ( 'T-fact'[EmployeeID] ) )
            RETURN
                CALCULATE (
                    SUM ( 'T-contract'[Hours per week] ),
                    'T-contract'[EmployeeID] = currentEmpID
                )
    )
RETURN
    SUMX ( _t, [@H/week] )
        Utilisation Rate = DIVIDE ( [H_worked], [Hours_per_week] )

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