Power BI Challenge 16 - Timesheet Utilisation Reporting from Jan

Challenge Updates
1

Here’s Jan’s entry for Power BI Challenge 16. @deltaselect, would you like to share how you built this dashboard and what your inspiration is in building it?

image

image

image

Here’s how Jan described it:

Hi everyone,

Please receive hereby my workout of the Challenge 16 - Timesheet Utilization Reporting.
It is a three page report: Utilization, Utilization defined and Sales analysis.

Straightforward Data Model and very limited data enrichment

  1. The data model exist of a date table, connected to the provided “ActualWorkData” of one consultant.
  2. A date column is added, derived from Start(time) for the connection with the date table, the Revenue column is deleted and calculated with a measure (Work * 24 * Hourly Rate + Bonus)

“Utilization defined” explains Business capacity and Utilization, with the Target settting.

  1. Capacity is calculated for only one person for all consecutive months from June 2018 till September 2021, as the data set contains billable worked hours of one person over this period.
  2. Definitions: (all expressed in hours)
    a) Gross business capacity: total days in a year * 8 hours excluding weekends, Dutch public holidays and five weeks of private holidays of the consultant per year, (in 2018 and 2021 pro rata).(Private holidays are scheduled, see table “Private Public Holidays”, and imported in the Date Table)
    b) Business capacity : gross business capacity -/- a generic variable illness percentage (default 3 %)
    c) Billable Target : business capacity x target percentage billable hours (default 65 %)
    d) Utilization : actual billable hours / business capacity
    (Capacity Measures setup accordingly)

“Utilization” reports actual billable hours against target, with rate trends and revenue development

  1. Main KPI is the utilization rate (gauge report), comparing actual versus target utilization.
  2. The target billable hours can be varied by changing the Target billable (default 65%) and the illness rate.
  3. Actual Sales is calculated at “worked” hours x hourly rate + bonus.
  4. The Avg act.sales price is derived from the Actual sales divided by the actual billable hours and is dependent on the chosen period
  5. The Benchmark price is calculated as total actual revenue over four years divided by total worked hours, and is used for target revenue, being the benchmark price x target billable hours.
  6. Act price trend is calculated as the percentage price difference between Avg act. sales price and benchmark
  7. Actual / target comparison in the two clustered column charts at the bottom have drilldown possibilities, starting with annual figures to remain overview.
  8. The year, or multiple or all years can be selected, which is valid for all three pages
    (Actual and price measures set up accordingly)

“Sales Analysis” gives insight in billable hours, price and sales per client

  1. Focus is set on customer sales analysis.
  2. Insight in sales per customer, ranking on base of sales, average price per customer and actual billable hours per customer, important to know if high utilization delivers also high price and sales.

Possible future utilization insights

  1. Comparison with previous periods
  2. Comparison utilization with other colleagues
  3. Utilization per project or task
  4. Actual billable hours versus average price per customer

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.

power-bi-chal-1

1 Like
2

This post is part of the Enterprise DNA platform improvements. Through these posts, members and non-members can showcase the resources and inspirations on how they come up with their challenge submissions. We hope all members can utilize it efficiently.

3

Nice one Jan. Thanks for participating.

Definitely some detailed analysis here. And really comprehensive for a consulting firm to use as a reporting tool.

Like how you’ve used grids in some way. And also a nice and simple navigation bar down the left hand side. These are the things that I definitely like here.

I would have gone with some colors that maybe gave it a little bit more pop but small and easy adjustment. I think it also maybe just aligning the visuals a little bit better. It sort of looks like you’ve selected the visuals and then just place them in a random location. So maybe next time just think about it a little bit more about how the eye moves across a page and how you want to tell a story as someone navigates through one page and then the next page and then the next.

I love how you’ve incorporated some text inside your visualizations as well. I think one of the underutilized aspects of Power BI is actually using text and also dynamic a text to showcase insights. Greg one of our experts does this really well.

You can also see you have used the parameter feature nicely here also. Almost creating some scenario analysis. I commend you on the nice work you’ve done with this analysis.

Congrats on a great submission.

Sam

1 Like