Forum Discussion
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 ...
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
Community Champion
Community ChampionAnonymous ,
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 , works like a charm!! Thank you so much.