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 ...
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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        ERD , works like a charm!! Thank you so much.