Dynamic table calculations based on slicer

[Anonymous]

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.

[prakash_gautam · Accepted solution]

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

[Anonymous]

Thanks Prakash!

Somehow I'd created a calculated column and not a measure. I'll go and pour some coffee in my eyes now...