Forum Discussion
Dynamic table calculations based on slicer
I have a relatively simple summarised table in PowerBI relating to working hours by employees. See below.
The table is a simple summary of Duration Hours and Billable Hours by User, and I'm using a Slicer to choose a required date range. There is also an additional Slicer to choose the Team. i.e. TeamA/TeamB/TeamC etc
Unfortunately, I'm having a problem creating '%BillableHours' column in the above for each individual User, and I have no idea where to start. I tried creating the following measure but without any luck...
Billable% = DIVIDE (SUM(tblTime[BillableHours]), SUM(tblTime[DurationHours]))
I think it needs to be modified to take into account the changing User, but I don't know how to do this. Any help greatly appreciated.
If you simply create a calculated field (measure) instead of a calculated column, your formula should work:
Billable% = DIVIDE (SUM(tblTime[BillableHours]), SUM(tblTime[DurationHours]))
If you want a calculated column for any reason, this should work:
Billable% =
CALCULATE (
SUM ( tblTime[BillableHours] ),
FILTER ( tblTime, tblTime[User] = EARLIER ( tblTime[User] ) )
)you can also add a AND condition if you want in the above formula as:
&& tblTime[Team] = EARLIER ( tblTime[Team])
Thanks
2 Replies
If you simply create a calculated field (measure) instead of a calculated column, your formula should work:
Billable% = DIVIDE (SUM(tblTime[BillableHours]), SUM(tblTime[DurationHours]))
If you want a calculated column for any reason, this should work:
Billable% =
CALCULATE (
SUM ( tblTime[BillableHours] ),
FILTER ( tblTime, tblTime[User] = EARLIER ( tblTime[User] ) )
)you can also add a AND condition if you want in the above formula as:
&& tblTime[Team] = EARLIER ( tblTime[Team])
Thanks