How can calculate the average of time values when it is in text "[h]:mm" format?

[Manav-Shah]

 

I have converted the time into [h]:mm format; however, the time duration is currently in string format. I need to find the average of the Time A-C column. I tried to change data type but it does not change and shows error. Help me to find the solution of this question.

[Anonymous]

Hi @Manav-Shah 

Here is my testing.

1. Create two calculated column as follow.

h = 
VAR TimeSecond = Sheet2[Time] * 86400
VAR NumberOfHour = QUOTIENT(TimeSecond, 3600)
VAR NumberOfMinutes = QUOTIENT( MOD(TimeSecond, 3600), 60)
RETURN
FORMAT(NumberOfHour, "[h]")

m = 
VAR TimeSecond = Sheet2[Time] * 86400
VAR NumberOfHour = QUOTIENT(TimeSecond, 3600)
VAR NumberOfMinutes = QUOTIENT( MOD(TimeSecond, 3600), 60)
RETURN
FORMAT(NumberOfMinutes, "mm")

2. Create a measure as follow

average = 
VAR _sum = SUM(Sheet2[h]) * 60 + SUM(Sheet2[m])
VAR _count = COUNTROWS(Sheet2)
VAR _averageSecond = DIVIDE(_sum, _count) * 60
VAR _NumberOfHours = QUOTIENT(_averageSecond, 3600)
VAR _NumberOfMinutes = QUOTIENT( MOD(_averageSecond, 3600), 60)
RETURN
FORMAT(_NumberOfHours, "[h]") & ":" & FORMAT(_NumberOfMinutes, "mm")

Is this the result you expect?

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Manav-Shah]

Hi @Anonymous ,
This result not match my actual result. The actual average-timing is 90:36.

[Anonymous · Accepted solution]

Hi @Manav-Shah 

You can modify the measure as follows.

average = 
VAR _sum = SUM(Sheet2[h]) * 60 + SUM(Sheet2[m])
VAR _count = COUNTROWS(Sheet2)
VAR _averageSecond = DIVIDE(_sum, _count) * 60
VAR _NumberOfHours = QUOTIENT(_averageSecond, 3600)
VAR _NumberOfMinutes = QUOTIENT( MOD(_averageSecond, 3600), 60)
RETURN
FORMAT(_NumberOfHours, "[h]") & ":" & FORMAT(_NumberOfMinutes, "[m]")

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Manav-Shah]

Hi @Anonymous ,

I have two data sheet in excel,
First sheet is below 

and second data sheet 

 These two data sheet I have in excel and I have to convert below data calculation in Power BI from these two data sheet.

[123abc]

In Power BI, you can calculate the average of time values in the "[h]:mm" format using DAX (Data Analysis Expressions). Here are the steps to achieve this:

Assuming your time values are in a column named "Time" in the format "[h]:mm", follow these steps:

1. Convert Text to Time:

   • First, create a new column to convert the text values to time values. Let's call this new column "TimeConverted".
   • Use the following DAX formula to convert the text to time:

TimeConverted = TIMEVALUE('YourTable'[Time])

1. Replace 'YourTable' with the actual name of your table.

2. Calculate Average:

   • After creating the "TimeConverted" column, you can calculate the average using the following DAX formula:

AverageTime = AVERAGE('YourTable'[TimeConverted])

Replace 'YourTable' with the actual name of your table.

This formula will give you the average time in decimal format. If you want to display it in the "[h]:mm" format, you can use the FORMAT function:

AverageTimeFormatted = FORMAT(AVERAGE('YourTable'[TimeConverted]), "[h]:mm")

Now, you can use the "AverageTimeFormatted" column in your Power BI report to display the average time in the desired format.

Make sure to replace 'YourTable' with the actual name of your table and adjust column names accordingly based on your dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Manav-Shah]

It still showing error.

[123abc]

It seems like there might be issues with the data in your columns. The error "Cannot convert value '89:02' of type text to type date" indicates that there might be some values in your columns that are not in a valid time format.

To address this issue, you can follow these steps:

1. Check Data Quality:

   • Review the values in columns A, B, and C to ensure that all the text values represent valid time values. Ensure that there are no anomalies or unexpected characters.

2. Handle Invalid Values:

   • Identify and handle any values that cannot be converted to a valid time format. You might need to clean or correct these values.

3. Ensure Consistency:

   • Make sure that all the values in the columns you are working with are consistently formatted in [h]:mm.

Here is an example of how you might handle invalid values and ensure consistency:

TimeColumn = IF(ISERROR(TIMEVALUE([YourTextColumn])), BLANK(), TIMEVALUE([YourTextColumn]))

Replace [YourTextColumn] with the actual column name that contains your time values in text format. This formula uses the TIMEVALUE function to attempt conversion, and if it encounters an error (indicating an invalid time format), it returns BLANK().

Once you've handled potential issues in your data, you should be able to calculate the average without encountering conversion errors.

If you still face issues or if you have specific examples of data causing problems, feel free to provide more details, and I can help you further troubleshoot the problem.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Manav-Shah]

I have time data in string format, I tried the Timevalues and Format function but it shows same error.