cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DMac86 New Member
New Member

Billable Utilization Rolled Up, Slice by Consultant

Hi guys,

 

Trying to build a Billable Utilization Chart that shows my full consulting practice's Bilalble Utilization (Billable Hours/Total Hours), but also gives me the ability to filter on the fly for an invidual consultant.

 

I have two tables:

1) Baseline Stats - 1 Row per month, includes number of billable consultants employed for that month, number of working hours available for that month, and the Month itself.

 

2) Billable Hours - 1 Row per month per consultant, includes the Month, the Consultant Name and the number of billable hours.

 

I've joined the tables using a 1-M relationship on Month, and created a Measure that divides SUM('Billable Hours'[Billable Hours]) by SUM('Baseline Stats'[Total Hours]). I'm then plotting that Measure on a line chart, by Month. 

 

The numbers are great if I put the Person field into the Legend of the chart, however what I'd like to do is have no legend (i.e. I want to show the full practice combined utilization), and use the Person field as a filter so I can drill into each one. However, when I remove the Person field from the Legend, the utiliation is way higher than it should be....looks like instead of dividing by the Total Number of Hours for each person, it's dividing by the total hours per month that is listed directly in the Baseline Stats table.

 

Put another way, this month might have a total of 170 Working hours according to the  Baseline Stats table, but I want the report to multiple that number by the number of employees that are in the Billable Hours table, AFTER filtering has been applied.

 

Thanks in advance, let me know if I can provide more information.

 

Dave

1 REPLY 1
Moderator v-qiuyu-msft
Moderator

Re: Billable Utilization Rolled Up, Slice by Consultant

Hi @DMac86,

 

In your scenario, would you please share some sample data of these two tables ( Baseline Stats,Billable Hours ) and screenshots about the desired results for our analysis?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.