cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II

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 2
Highlighted
Resolver II

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

Highlighted
Frequent Visitor

Thanks Prakash!

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

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors