How to Calculate Total Hours in Power BI

Calculating total hours in Power BI seems simple, but it often confounds users when their totals exceed 24 hours. If you've ever tried to sum a column of hours worked and gotten a bizarre result like "6:00" instead of "30:00," you're in the right place. This guide will walk you through the correct ways to calculate and display time durations in Power BI, ensuring your reports are accurate and easy to understand.

Why Summing Time Can Be a Headache in Power BI

The core of the problem lies in how Power BI interprets time data. By default, a 'Time' data type represents a specific point in time during a 24-hour day (e.g., 5:00 PM). It's not designed to represent a duration (e.g., 5 hours long).

When you try to add up a column formatted as Time, Power BI treats it like adding time on a clock. For example, if you have these three shifts:

• 10 hours
• 10 hours
• 10 hours

You expect the total to be 30 hours. However, Power BI calculates 30 hours from the start of a day. This is one full day (24 hours) plus 6 hours. As a result, it will often display the result as 6:00:00 AM, which is completely wrong for your report.

To get the correct total, we need to convert the time into a numerical value (like total seconds or total hours as a decimal) that can be summed correctly. Then, we can format that total back into a readable time format. Let's explore the best ways to do this.

Method 1: Calculating Duration Between Start and End Times with DATEDIFF

A common starting point is a dataset with a 'Start Time' and an 'End Time' for activities like employee shifts, project tasks, or machine operation. The goal is to calculate the duration of each activity first and then sum those durations.

The easiest way to calculate this difference is by creating a calculated column in your data table using the DATEDIFF function in DAX.

Step-by-Step Instructions:

1. Navigate to the Data view in Power BI and select the table that contains your start and end times.
2. From the toolbar, click on New column.
3. In the formula bar, enter a DAX formula to calculate the difference. It's often best practice to calculate the difference in the smallest unit you need (like seconds or minutes) for better precision.

Let's say you want to calculate the duration in minutes. The formula would look like this:

Duration (Minutes) = DATEDIFF('YourTable'[Start Time], 'YourTable'[End Time], MINUTE)

Or, to get it as a decimal representation of hours:

Duration (Hours) = DATEDIFF('YourTable'[Start Time], 'YourTable'[End Time], SECOND) / 3600

After creating this column, Power BI will calculate the duration for each row. Because this new column is a regular number (either an integer for minutes or a decimal for hours), you can now create a simple measure to sum it:

Total Hours = SUM('YourTable'[Duration (Hours)])

This works perfectly. The challenge, however, is that you might end up with a total like "257.75" hours. While accurate, it's not always the most user-friendly format. The next method addresses how to turn these decimal numbers into a more familiar [HH]:MM:SS format.

Method 2: Creating a DAX Measure to Sum and Format Total Hours

This is the most powerful and flexible method. It's perfect for when you have a column of durations and want a grand total that correctly displays as hours, minutes, and seconds, even if it's over 24 hours.

We'll create a DAX measure that does three things:

1. Calculates the total duration in seconds.
2. Converts those seconds into corresponding integers for hours, minutes, and seconds.
3. Formats the result into a clean text string like "HH:MM:SS".

Step 1: Calculate Total Seconds

First, create a basic measure that calculates the total time in seconds. Assuming you have a column named [Session Duration] that's already in a time/duration format, we need to convert each value to seconds and sum them up.

Total Seconds = 
SUMX(
    'YourTable',
    HOUR('YourTable'[Session Duration]) * 3600 +
    MINUTE('YourTable'[Session Duration]) * 60 +
    SECOND('YourTable'[Session Duration])
)

This SUMX formula iterates through every row of your table, converts the time duration from that row into seconds, and then adds it all up.

Step 2: Create the Formatted Duration Measure

Now, let's build the final measure. This pattern is incredibly useful and reusable for any kind of duration formatting.

1. On the Home ribbon, click New measure.
2. Enter the following DAX formula into the formula bar. We use variables (VAR) to keep the formula clean and efficient.

Formatted Total Time = 
VAR TotalSeconds = [Total Seconds] // Reference the measure from Step 1
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)

// Format the values into a time string like "HH:MM:SS"
RETURN
FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")

Breaking Down the Formula:

• VAR TotalSeconds: Stores the total seconds calculated previously.
• VAR Hours: Gets the whole hours from total seconds.
• VAR Minutes: Finds remaining minutes after subtracting hours.
• VAR Seconds: Finds remaining seconds after subtracting hours and minutes.
• RETURN: Combines all parts into a string with leading zeros where needed.

Now you can add this [Formatted Total Time] measure to a Card visual or a table, and it will correctly display totals like "35:15:42", even when the hours go past 24.

Important Note: Because this measure returns a text string, you can't use it in further calculations (like finding an average). For that, you would use the [Total Seconds] measure. It's often a good practice to have both: one for calculation and one for display.

Method 3: Preparing Your Data in Power Query

Sometimes, it's easier to handle data transformations before your data even gets into the Power BI data model. This is where Power Query comes in. By shaping the data on import, you can make your DAX formulas much simpler.

Step-by-Step Instructions:

1. From the Home ribbon, click on Transform data to open the Power Query Editor.
2. Select the query (your table) that contains your time data.
3. If you have Start Time and End Time columns, you can create a duration column easily. Select the End Time column, then hold Ctrl and select the Start Time column (order matters!).
4. Go to the Add Column tab, click on Time, and then select Subtract. This creates a new column with a Duration data type.
5. Now, with the new Duration column selected, go back to the Add Column tab and find the Duration button. From here, you can extract the duration as a single numeric value, such as Total Hours, Total Minutes, or Total Seconds.
6. Choose Total Hours. Power Query will create a new column with the duration represented as a decimal number (e.g., 2.5 for two hours and thirty minutes).
7. Click Close & Apply on the Home ribbon to load your transformed data into Power BI.

Once you've done this, you have a simple decimal number column in your data model. Calculating the total is as easy as creating a basic sum measure:

Total Hours (PQ) = SUM('YourTable'[Total Hours])

You can then use the DAX formatting method from above if you need to display this decimal sum in the HH:MM:SS format.

When to Use Power Query vs. DAX?

• Use Power Query when you want a permanent transformation of your raw data. It simplifies your DAX model and can improve performance on very large datasets.
• Use DAX when you need more dynamic calculations that respond to filters and slicers in your report. DAX measures are generally more flexible.

Final Thoughts

Calculating and summing hours in Power BI becomes straightforward once you understand that you need to convert time into numbers before aggregating. Whether you prefer the flexibility of a detailed DAX measure or the upfront data wrangling of Power Query, both paths lead to accurate and readable reports that a simple sum could never achieve.

Mastering these types of calculations is a big part of becoming proficient with tools like Power BI, but it also highlights the steep learning curve involved. At Graphed we felt this pain - spending more time fighting formulas than finding answers - and decided there had to be an easier way. We built an AI data analyst that lets you connect your data sources and build dashboards just by asking questions in plain English. Instead of learning DAX, you can simply ask, "What were the total hours worked per project last month?" and get your answer in seconds, allowing you to focus on the insights, not the setup.