Good day,
I have a data model that connects time entries with members and daily available hours; 'Dailyhours', 'Members', 'Time'. The visual i created calculates time billed and evaluates against hours that were available to bill for each given date in a date range. The available hours total based on member start dates and can vary in the date range per member. The measure I created to calculate the available hours for each member is:
MeasureAdj =
VAR endate = MAX ('dailyhours'[Date])
VAR startdate = MIN('v_rpt_Member'[Date_Hire])
Return
if(CALCULATE(
sum(dailyhours[AvailableHours]),
'dailyhours'[Date] >= startdate && 'dailyhours'[Date] <= endate)< sum(dailyhours[AvailableHours]),
CALCULATE(
sum(dailyhours[AvailableHours]),
'dailyhours'[Date] >= startdate && 'dailyhours'[Date] <= endate), sum(dailyhours[AvailableHours]))
The visual creates this result.
Hours Total Billable Hours % Util (BillableHrs/MeasureAdj) MeasureAdj
2143.61 684.00 34.5% 2032
2133.75 1686.00 83.0% 2032
463.25 121.00 30.1% 408
2162.50 1997.00 98.3% 2032
2155.06 1959.00 96.4% 2032
2099.00 1875.50 92.3% 2032
2310.39 1883.50 92.7% 2032
1682.12 918.50 58.6% 1568
2413.52 1394.00 69.3% 2032
576.08 422.75 80.1% 528
2128.59 1635.50 80.5% 2032
T: 20,267.87 14,576.75 719.0% 2032
The total for % Util is not correct - it calculating the 719% based on every line using 2032 as available hours and I really need it to show the average utilization v. a totak. I also need the MeasureAdj column to total all the values in that column.
Any ideas appreciated.
Thanks.
