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.

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