cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

Table.JPG

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
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

 

View solution in original post

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

 

View solution in original post

Highlighted

Thanks Prakash!

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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