How to Create a Utilization Report in Power BI

Creating a utilization report can feel like a daunting task, but it’s one of the most powerful ways for a service-based business to understand team performance and profitability. This report clarifies how your team’s time is spent, breaking it down between billable client work and essential non-billable activities. In this tutorial, we will walk through exactly how to prepare your data, write the necessary formulas, and build an interactive utilization report using Microsoft Power BI.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Utilization Report?

At its core, a utilization report measures the productivity of your employees or team by comparing billable hours to their total available hours. It helps managers answer critical questions like:

• Are my team members over or under-resourced?
• Which projects are consuming the most time?
• How much of our work is directly generating revenue versus supporting internal operations?
• Do we have enough staff to take on a new client?

The key metric is the utilization rate, typically calculated as:

(Total Billable Hours / Total Available Hours) x 100

A sustainable target rate is often between 70-80%, leaving room for non-billable tasks like professional development, administrative work, and a healthy work-life balance. Tracking this helps professional services firms, agencies, and consulting teams stay profitable and keep projects on track.

Step 1: Prepare Your Data for Power BI

Before you even open Power BI, the most important step is getting your data organized. Accurate reporting is entirely dependent on clear, well-structured source data. You'll generally need data from a time-tracking system (like Harvest or Clockify) exported into a spreadsheet. For this report, we'll imagine you have two primary data tables.

The Timesheet Table

This is the heart of your report and should contain every time entry logged by your team. It needs at least these columns:

• Employee Name: The name of the person who logged the hours.
• Project Name: The project associated with the time entry.
• Date: The date the work was performed.
• Hours Logged: The number of hours worked for that entry.
• Is Billable?: A field that clearly marks the work as "Billable" or "Non-Billable."

Your data might look something like this:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Employee Capacity Table

To calculate utilization rates against capacity, you need a table specifying how many hours each team member is expected to work. This can be a simple table:

• Employee Name: The team member's name (must match the Timesheet table).
• Weekly Capacity: Their standard weekly working hours (e.g., 40).

Your data might look like this:

With these two tables ready, you can now launch Power BI and import them.

Step 2: Load and Model Your Data in Power BI

Once you have Power BI Desktop open, the first actions are to load and start relating your data tables.

1. Load Your Data

Go to the Home tab, click Get Data, and select the source of your files (e.g., Excel workbook). Select your Timesheet and Employee Capacity files to load them into Power BI.

2. Create a Calendar Table

For any time-based analysis in Power BI, using a dedicated calendar table is a best practice. It gives you more flexibility when analyzing trends. You can create one quickly with DAX (Data Analysis Expressions).

Go to the Modeling tab and click New Table. In the formula bar, paste this DAX code:

Calendar = CALENDARAUTO()

This automatically creates a calendar table with a continuous list of dates based on the earliest and latest dates in your entire dataset.

3. Create Table Relationships

Now, let's connect the tables. Go to the Model view (the third icon on the left-hand panel).

• Drag the Date field from your Calendar table and drop it onto the Date field in your Timesheet table.
• Drag the Employee Name field from your Employee Capacity table and drop it onto the Employee Name field in your Timesheet table.

This creates one-to-many relationships, allowing visuals to filter data across all your tables correctly.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Write DAX Measures to Perform Calculations

Measures are calculations that you create to perform calculations that you’ll need for your visual charts. Think of them as the engine of your report. From the Report View, right-click any blank space in the Data pane and select New measure to begin entering formulas.

Let's create the essential measures for our utilization report.

Total Hours Logged

This simply adds up all the hours from your Timesheet table.

Total Hours = SUM(Timesheet[Hours Logged])

Billable Hours

Here, we sum the hours but only for entries marked as "Billable."

Billable Hours = CALCULATE([Total Hours], Timesheet[Is Billable?] = "Billable")

Non-Billable Hours

Similarly, this isolates the non-billable time.

Non-Billable Hours = CALCULATE([Total Hours], Timesheet[Is Billable?] = "Non-Billable")

Total Capacity Hours

Here you’ll leverage your Employee Capacity data and your calendar to generate the correct capacity relative to dates. This measure calculates the total available working hours for the selected period.

Total Capacity = SUMX(VALUES('Calendar'[Date]),SUMX('Employee Capacity',[Weekly Capacity]))

Utilization Rate

Finally, we calculate the utilization rate. The DIVIDE function is used to safely handle any potential division by zero errors.

Utilization Rate = DIVIDE([Billable Hours], [Total Capacity], 0)

Once you are done making your DAX measures, ensure they use the right data type, especially Utilization Rate, which should be a Percentage (%) in the Measure Tools menu in the top ribbon menu once selected.

Step 4: Build Your Report Visuals

With your data model set and measures created, it's time for the fun part: visualizing the information.

1. High-Level KPIs

Start with some clear, headline numbers. In the Visualizations pane, select the Card visual. Create three separate cards and drag in your new measures:

• A card for Utilization Rate
• A card for Billable Hours
• A card for Non-Billable Hours

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Utilization by Employee

A stacked bar or column chart is perfect for comparing team members.

• Choose the Stacked column chart visual.
• On the X-axis, add Employee Name from your Employee Capacity table.
• On the Y-axis, add both Billable Hours and Non-Billable Hours.

This immediately shows you the total hours for each person and how that time is broken down.

3. Billable vs. Non-Billable Mix

To see the overall mix, a Donut chart is a great choice.

• Select the Donut chart visual.
• For the Values, drag in Billable Hours and Non-Billable Hours. You'll get a simple, powerful view of your overall resource allocation.

4. Trend Over Time

How does utilization change week-to-week or month-to-month? A Line chart will tell the story.

• Select the Line chart visual.
• Drag the Date field from your Calendar table to the X-axis.
• Drag the Utilization Rate measure to the Y-axis.

5. Adding Interactivity with Slicers

Empower users to explore the data by adding slicers. Select the Slicer visual from the Visualizations pane. Create slicers for:

• Date Range: Use the Date field from your Calendar table. You can format it as a "Between" slicer for easy date picking.
• Employee Name: Use the Employee Name field so managers can filter to a specific person.

Final Thoughts

Building a utilization report in Power BI transforms raw timesheet data into a strategic asset. By preparing your data, writing a few key DAX measures, and arranging clear visuals, you can uncover critical insights about workload management, project profitability, and team productivity that were previously hidden in spreadsheets.

While creating these dashboards in a powerful tool like Power BI is a crucial skill, the setup, data modeling, and DAX learning curve can feel substantial. At Graphed, we’ve focused on eliminating that technical hurdle. You can simply connect your data sources - whether they're spreadsheets or SaaS tools - and ask for what you need in plain English: "Create a dashboard showing overall team utilization rate versus our 80% goal for this quarter." We handle all the background steps to build a real-time, interactive dashboard in seconds, letting you focus entirely on acting on your data. Get a jumpstart on your reporting needs with Graphed today.