Forum Discussion
Combining multiple rows in data table with calculation
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!
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) & """"
1 Reply
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) & """"