Resource Utilization - Power BI Reports

Video has been removed from this PDF. Visit the BrightWork 365 knowledge base to view.

2023-09

Introduction

In addition to Work Allocation reports, BrightWork 365 also offers a Resource Utilization Power BI Dashboard, allowing users to view all the project task assignments across projects, and assess levels of resource utilization. The dashboard allows viewers to quickly determine whether a resource is under or over allocated with project tasks.


Dashboard Components

Legend

The legend provides a definition of the color-coding present throughout the Main Window, making it easier for users to understand the data they're viewing.

Main Window

The main report window provides details about the chosen timeframe currently being analyzed including:

  • All users with tasks assigned to them.
  • The projects related to users that have been assigned tasks.
  • All related tasks that have been assigned to users.
  • The number of hours assigned to users (see below for how this is calculated).

The Main Window also allows users to interact with the data, letting them drill down into more granular details about the assigned tasks.

Use the various Power BI right-click and focus options which provide the ability to Expand all/Collapse all, Drill up/Drill down (for example to switch from a Week view to a Month view), etc.


 

Date Range

Through the use of drop-down menus, users can change the Date Range displayed in the main window. This section also allows you to click on an individual resource to focus solely on their work utilization.

Project Utilization Summary

View a summary of Hours and Start and Finish Dates for projects with work that fits within the chosen Date Range. Click on the name of a project to open the project in a new browser tab.

Hours Assigned out of Available

This section of the report displays for the selected user for the chosen Date Range, the total hours assigned out of the total hours available, the % Allocation, and the % Availability.

When viewing the Resource Utilization Power BI reports, the Task Unit % for a resource that was entered in the Gantt or Assignment will be applied to the reports, which account for the variation of % utilization. For example, a Monday to Friday task (equal to 40 hours of work), with a Task Unit % of 50 for a resource, will show up as 4 hours per day for 5 days duration for the resource.

Filters

Filter the displayed data by available criteria.


Resource Utilization Adjustment Process

When adjusting resourcing, the Project Manager can launch the resource report in the Power BI service, and download the related PBIX file to open the new resource report in Power BI Desktop. They can then make assignment adjustments in the BrightWork 365 app and view the effects of these changes in realtime using Power BI Desktop, which allows unlimited refreshes of the report data unlike other methods of using Power BI which have limited daily refreshes. This cycle of Adjust in the app > View in Power BI Desktop > Adjust in the app, can be repeated as often as necessary to balance resource workloads.

Below are details for using the Resource Utilization Adjustment Process.

Open in Power BI

The Open in Power BI link will open the report in the Power BI Service, displaying the report in a separate webpage.

Download the Report PBIX File

Power BI Desktop is a free download from Microsoft.

  • Users require Contributor permission in the relevant Power BI workspace to download the PBIX file.
  • When users with Contributor permission access the Power BI My Work Dashboard, instead of only seeing their own work, they will see all users' work.

When using Power BI Desktop for resource utilization purposes be sure not to publish the report as this will affect all users of the report.

One of the added functions provided by the Power BI Service is the ability to download the PBIX file that is associated with the report, which can then be opened in Power BI Desktop. To download the file from the Power BI Service:

  1. Click File > Download this file.
  2. Choose to download a copy of your report and data.

Open the PBIX File in Power BI Desktop

  1. When file download initiated above is complete, open the PBIX file in Power BI Desktop.
  2. View the data in Power BI Desktop and choose to refresh the data as necessary using the Refresh option.

If values are missing for columns Hour and Alloc in Power BI Desktop when all rows are collapsed, expand any row and the missing values will be displayed.


Utilization Calculation Rules

Resource Utilization - Task

  • Resource utilization for task assignments is noted in Hours and is derived from the formula # of Days * 8.

Resource Availability - Hours

  • Each assignee's availability is set to 40 hours per week by the BrightWork 365 solution.

Resource Availability - Calendar

  • The solution uses one simple Monday to Friday Calendar.

Utilization Threshold Colors

For the various week time range intervals, the following colors code will appear in each cell for hours allocated:

If value < 1 hours then White will appear in the relevant cell.

If value >= 1 hours and < 40 hours then Grey will appear in the relevant cell.

If value = 40 hours then Green will appear in the relevant cell.

If value > 40 hours then Red will appear in the relevant cell.

Two or more tasks assigned to a resource on the same day will always display the user as over allocated; the system assumes one day of work for each assigned task even if there are other assignees. For example, if Dan is assigned two tasks on the same day, he will show as 200% allocated for that day.

2023-06

Introduction

In addition to Work Allocation reports, BrightWork 365 also offers a Resource Utilization Power BI Dashboard, allowing users to view all the project task assignments across projects, and assess levels of resource utilization. The dashboard allows viewers to quickly determine whether a resource is under or over allocated with project tasks.


Dashboard Components

Legend

The legend provides a definition of the color-coding present throughout the Main Window, making it easier for users to understand the data they're viewing.

Main Window

The main report window provides details about the chosen timeframe currently being analyzed including:

  • All users with tasks assigned to them.
  • The projects related to users that have been assigned tasks.
  • All related tasks that have been assigned to users.
  • The number of hours assigned to users (see below for how this is calculated).

The Main Window also allows users to interact with the data, letting them drill down into more granular details about the assigned tasks.

Use the various Power BI right-click and focus options which provide the ability to Expand all/Collapse all, Drill up/Drill down (for example to switch from a Week view to a Month view), etc.


 

Date Range

Through the use of drop-down menus, users can change the Date Range displayed in the main window. This section also allows you to click on an individual resource to focus solely on their work utilization.

Project Utilization Summary

View a summary of Hours and Start and Finish Dates for projects with work that fits within the chosen Date Range. Click on the name of a project to open the project in a new browser tab.

Hours Assigned out of Available

This section of the report displays for the selected user for the chosen Date Range, the total hours assigned out of the total hours available, the % Allocation, and the % Availability.

Filters

Filter the displayed data by available criteria.


Resource Utilization Adjustment Process

When adjusting resourcing the Project Manager can launch the resource report in the Power BI service, and download the related PBIX file to open the new resource report in Power BI Desktop. They can then make assignment adjustments in the BrightWork 365 app and view the effects of these changes in realtime using Power BI Desktop, which allows unlimited refreshes of the report data unlike other methods of using Power BI which have limited daily refreshes. This cycle of Adjust in the app > View in Power BI Desktop > Adjust in the app, can be repeated as often as necessary to balance resource workloads.

Below are details for using the Resource Utilization Adjustment Process.

Open in Power BI

The Open in Power BI link will open the report in the Power BI Service, displaying the report in a separate webpage.

Download the Report PBIX File

Power BI Desktop is a free download from Microsoft.

  • Users require Contributor permission in the relevant Power BI workspace to download the PBIX file.
  • When users with Contributor permission access the Power BI My Work Dashboard, instead of only seeing their own work, they will see all users' work.

When using Power BI Desktop for resource utilization purposes be sure not to publish the report as this will affect all users of the report.

One of the added functions provided by the Power BI Service is the ability to download the PBIX file that is associated with the report, which can then be opened in Power BI Desktop. To download the file from the Power BI Service:

  1. Click File > Download this file.
  2. Choose to download a copy of your report and data.

Open the PBIX File in Power BI Desktop

  1. When file download initiated above is complete, open the PBIX file in Power BI Desktop.
  2. View the data in Power BI Desktop and choose to refresh the data as necessary using the Refresh option.

If values are missing for columns Hour and Alloc in Power BI Desktop when all rows are collapsed, expand any row and the missing values will be displayed.


Utilization Calculation Rules

Resource Utilization - Task

  • Resource utilization for task assignments is noted in Hours and is derived from the formula # of Days * 8.
  • The Duration of task assignments is attributed equally to all assignees of tasks, e.g., if a Task is assigned to 3 people for 1 day - then each of the 3 people get allocated 8 Hours each.
  • Each assignee's utilization is set at 100% per Task.

Resource Availability - Hours

  • Each assignee's availability is set to 40 hours per week by the BrightWork 365 solution.

Resource Availability - Calendar

  • The solution uses one simple Monday to Friday Calendar.

Utilization Threshold Colors

For the various week time range intervals, the following colors code will appear in each cell for hours allocated:

If value < 1 hours then White will appear in the relevant cell.

If value >= 1 hours and < 40 hours then Grey will appear in the relevant cell.

If value = 40 hours then Green will appear in the relevant cell.

If value > 40 hours then Red will appear in the relevant cell.

Two or more tasks assigned to a resource on the same day will always display the user as over allocated; the system assumes one day of work for each assigned task even if there are other assignees. For example, if Dan is assigned two tasks on the same day, he will show as 200% allocated for that day.