topic Re: Combining Hours and Minutes then Averaging in DAX Commands and Tips

Combining Hours and Minutes then Averaging

TBSST — Thu, 19 Oct 2023 01:20:04 GMT

Hi All,

I am trying to find the average of time spent when doing surveys.

The hours and minutes spent are in different columns.

I cant seem to find how to combine the times together then find the average time spent umong the surveys.

Any help will be greatly appreciated.

Thank you.

Re: Combining Hours and Minutes then Averaging

vicky_ — Thu, 19 Oct 2023 06:10:48 GMT

Is there any chance that you can create a column and do all your calculations in minutes?
When you get the result that you want (in minutes), you can then adapt one of these to format as hh:mm:

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

Re: Combining Hours and Minutes then Averaging

123abc — Thu, 19 Oct 2023 06:12:41 GMT

To find the average time spent on surveys in Power BI when the hours and minutes are in different columns, you can follow these steps:

Assuming you have two columns named "Hours" and "Minutes" to represent the time spent on surveys, you can combine them into a single column and then calculate the average. Here's how you can do it:

Combine Hours and Minutes into a Single Column: You can create a custom column that combines the hours and minutes into a single time duration column. You can use the Power Query Editor to do this. Follow these steps:
- Open Power BI Desktop.
- Go to "Home" > "Edit Queries" to open the Power Query Editor.
- Select the table containing your survey data.
- Go to the "Add Column" tab and select "Custom Column."
In the "Custom Column" dialog, use a formula like this to combine hours and minutes:

Duration.From([Hours] & ":" & [Minutes])

This will create a new column that combines the hours and minutes into a time duration.
Calculate the Average Time: After you've combined the hours and minutes into a single column, you can now calculate the average time spent. Here's how:
- Close the Power Query Editor, and you'll see the new combined column in your data table.
- Create a new measure to calculate the average duration. Go to the "Modeling" tab and select "New Measure."
- In the formula bar for the new measure, use the AVERAGE function to calculate the average duration:

Average Duration = AVERAGE('YourTableName'[CombinedTimeColumn])

Replace 'YourTableName' with the name of your data table and 'CombinedTimeColumn' with the name of the column where you combined hours and minutes.

Now you have a measure called "Average Duration" that gives you the average time spent on surveys. You can add this measure to your visuals and reports to display the average time.

Make sure that the "CombinedTimeColumn" in your model is set as a duration data type so that the AVERAGE function can calculate the average correctly.

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.

Re: Combining Hours and Minutes then Averaging

Anonymous — Fri, 20 Oct 2023 07:04:18 GMT

Hi TBSST ,

Based on your description, I created the simple sample, you may check the result:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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