Forum Discussion
Yggdrasill
Responsive Resident
6 years agoCalculate correct total for a ratio
Hello. I've been struggling with something that seemed to be rather easy but somehow it isn't. I have 3 tables. One factTable with summarized hours workers register. I have one employee table...
SQLbyoBI
Advocate I
6 years agoare we to assume these calculations will only ever be used in the context of a single employee, or should the calculation be able to handle scenarios where there are multiple employees in context?
If we assume a single employee, then this should help...
Mandatory =
VAR _mandatoryHours = 7.5 //mandatory hours per day
VAR __cur_resource_last_date = SELECTEDVALUE( Resource[EndDate] )
//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
CALCULATE(
SUM( Calendar[WorkRatio] ) * _mandatoryHours,
FILTER(
'Calendar',
AND(
'Calendar'[Date] <= 'Last Refreshed'[LastRefreshed],
'Calendar'[Date] <= __cur_resource_last_date
)
)
)
* AVERAGE( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
IF(
ISINSCOPE( 'Calendar'[Date].[Year] ),
_daycalc,
_calc
)
EVALUATE
( 'calendar' )