How to Sum Time in Power BI

Trying to sum a column of time values in Power BI can be surprisingly frustrating. A simple drag-and-drop or a standard SUM() function doesn’t work, usually resulting in an error. This article will walk you through the correct way to sum durations in Power BI using DAX, ensuring you get accurate totals presented in a clean HH:MM:SS format.

Why Is Summing Time in Power BI Tricky?

The core issue lies in how Power BI handles time data. When you import a column with values like "01:30:15" (1 hour, 30 minutes, 15 seconds), Power BI typically assigns it a DateTime data type. This format is designed to represent a specific point in time (e.g., January 1, 1900, at 1:30:15 AM), not a duration.

Because of this, an aggregate function like SUM() doesn't understand how to add these point-in-time values together. It’s like trying to add "2:00 PM" to "4:00 PM" - it doesn't logically result in "6:00 PM." To sum durations properly, you first need to convert your time values into a consistent numerical unit that Power BI can actually add up, such as total seconds.

The Best Method: Convert to Seconds, Sum, and Reformat

The most reliable method involves a three-step process: First, create a new column that converts each time value into total seconds. Second, create a measure to sum that new column. Third, create another measure to format the total seconds back into a familiar HH:MM:SS display format.

Let's walk through it with an example. Imagine you have a table named TicketData with a column called [Handling Time] containing the duration spent on each support ticket.

Step 1: Create a Column with Duration in Seconds

First, we need to convert the [Handling Time] values into a pure number we can work with. Seconds is the easiest unit.

Navigate to the Data view in Power BI (the grid icon on the left).
Select the table containing your time data (TicketData in our example).
From the top ribbon, click on Column tools and then New column.
In the formula bar that appears, enter the following DAX formula:

Let’s quickly break down this formula:

HOUR([Handling Time]) * 3600: Extracts the hour component from your time value and converts it to seconds (1 hour = 3600 seconds).
MINUTE([Handling Time]) * 60: Extracts the minute component and converts it to seconds (1 minute = 60 seconds).
SECOND([Handling Time]): Extracts the seconds component.

After you press Enter, you’ll have a new column named Handling Time in Seconds filled with the total seconds for each row.

Step 2: Create a Measure to Sum the Seconds

Now that you have a numeric column, summing it is easy. For this, we'll create a measure, which is better for this kind of aggregation than another calculated column.

While still in the Data view or back in the Report view, click on New measure from the ribbon.
Enter this simple SUM formula in the formula bar:

This measure now correctly calculates the grand total of all your durations, but it's just a large number of seconds. If you used it in a Card visual, you’d see something like 91345 instead of a user-friendly time format.

Step 3: Create a Formatted Measure for Display (HH:MM:SS)

The final step is to convert the total seconds from your measure back into a readable HH:MM:SS format. This is where a bit of DAX magic comes in to calculate the hours, minutes, and leftover seconds.

Create another New measure.
Enter the following DAX formula. This formula intelligently reconstructs the time format and is the best practice for handling totals that might exceed 24 hours.

How This DAX Formula Works

VAR: This defines variables to store each part of our calculation, making the formula clean and easy to read.
Days: This calculates the number of full days (1 day = 86,400 seconds) in the total.
Hours: MOD ( TotalSeconds, 86400 ) gives us the remaining seconds after accounting for full days. We then divide that by 3600 to get the hours left over.
Minutes: MOD ( TotalSeconds, 3600 ) calculates the remaining seconds after subtracting all the full hours. We then divide by 60 to find the minutes.
RemainingSeconds: The final MOD function gives us the leftover seconds after accounting for minutes.
The RETURN statement pieces it all together. It adds the converted day total back into the hours section, then uses the ampersand (&) to concatenate the hours, minutes, and seconds with colons in between. The FORMAT(..., "00") part ensures each component has a leading zero if it's a single digit (e.g., 07 instead of 7).

Now, you can use the [Total Handling Time Formatted] measure in your report visuals, like a Card or a table, and it will display the sum perfectly. To solve any large number formatting, you shouldn't turn on aggregation for measures, as these are calculated after filtering has happened.

Handling Time Durations That Exceed 24 Hours

A common pitfall is that some simpler formatting methods in Power BI will break when the total duration goes over 24 hours. For example, a total of 25 hours might incorrectly display as 01:00:00.

The formula provided in Step 3 is specifically designed to avoid this problem. By first converting all the extra time in the Days VAR variable into workable units in the Hours format, you ensure that a sum of 26.5 hours will correctly appear as 26:30:00 not 02:30:00.

Final Thoughts

While Power BI doesn't offer a direct way to sum time out-of-the-box, the process is straightforward with DAX. By converting your time values into total seconds, summing them up with a measure, and then using a final formatting measure, you can display accurate and readable time duration totals in all of your reports.

The process of converting and formatting data is a common need in data analysis that we understand well. Many teams find themselves stuck building DAX measures, whether it's for summing durations from CRM call logs or calculating advertising campaign run times. With a tool like Graphed you can skip hours of creating formulas and simply and securely connect tools like HubSpot or Google Ads, where you create complex formatted dashboards with simple, conversational prompts instead, like “calculate the total call duration by sales rep for last month, showing the day-by-day comparison as a line chart that has been formatted" which means you get your answers instantly, no DAX headaches required.