Combining multiple rows in data table with calculation

[Anonymous]

Hi Everyone,

I'm building a report using Power BI and am stuck on a calculation i'm trying to achieve. I have an excel file; with a couple of columns. For the sake of this example, let's refer to the 'Name', 'Date' and 'AHT' where the AHT column is a time column measured in HH:MM:SS.

There are multiple rows in the excel data though for the same name as can be seen in my screenshot below.

What i'm looking to do is to perform a calculation that takes all of the rows for a single month - let's say January; and then calculate the average of time as seen in the 'AHT' column - hopefully that makes sense. Is this possible to do?

Thanks very much in advance!

[amitchandak · Accepted solution]

@Anonymous , You have to create time in second and then take avg

New colum=  hour([AHT])*3600 + minute([AHT])*60 + second([AHT])

Not you can create a meausre

Avg AHT = average([New column])

You can use new dynamic formatting

use a format like

"""" & QUOTIENT([net time],3600) & ":" & QUOTIENT(mod([net time],3600),60) & ":" & round(mod(mod([net time],3600),60),0) & """"