Forum Discussion
Helper IGROUPBY Returning an Error, Please Help.
[Total Time] = SUM( T[Decimal Time] ) [Billable Time] = var __breakTime = 1 * time(0, 30, 0) var __result = SUMX( SUMMARIZE( T, T[UserID], T[Date] ), var __time = [Total Time] var __deduction = ( __time > 5 ) * __breakTime return __time - __deduction ) return __result [Billable Time %] = DIVIDE( [Billable Time], [Total Time] )Best
D
Helper IHi D,
In that case, how would you suggest I tackle the below?
I have some time record data which includes Date, User, Time(duration) and whether the line is billable time or not. The dataset has many different users and contains a whole week of data. I would like to calculate the percentage of billable time for each user. However, if the person worked more than 5 hours on a given date, the calculation needs to deduct a 30 minute lunch break from their 'non-billable' time. I have almost succeeded with a measure, but the date is not taken into account. So the time taken out to account for lunch breaks is innacurate. Currently, the calculation sees that User A has worked 38 hours total, so it minuses 30 minutes. I need it to check how long the person worked on every date, and minus 30 minutes for every time the person worked more than 5 hours.
The reason why I want this to be a dax measure is because the calculation will also be used to populate a Pivot Table in Excel.
Do you have any suggestions?
Thank you again.
[Total Time] = SUM( T[Decimal Time] )
[Billable Time] =
var __breakTime = 1 * time(0, 30, 0)
var __result =
SUMX(
SUMMARIZE(
T,
T[UserID],
T[Date]
),
var __time = [Total Time]
var __deduction = ( __time > 5 ) * __breakTime
return
__time - __deduction
)
return
__result
[Billable Time %] =
DIVIDE(
[Billable Time],
[Total Time]
)
Best
D
- jaime_blackwell
Thank you so much D!