AVERAGE measure produce error

[christianMDU]

Posted this earlier but a moderator thought it was spam... It isn't.

I have only just begun using Power BI and I'm following along with some YouTube videos. I thought I had a good grip, so would create a visual off of my own data, but I am stuck!

I have a spreadsheet with a number of columns in. The most interesting ones are labelled "Item ID" (a unique reference) and "Time to Accept" which is hh:mm:ss. I want a card to show me the average time to accept value across each of my rows. I've selected the "Item ID" field and the "Time to Accept" fields which has produced this table:

I've then clicked Home -> New measure and entered "Average Time to Accept = AVERAGE(Sheet[Time to Accept])" but now the visual displays an error "Can't display the visual":

When I click "See details", here's what I see:

[v-rongtiep-msft]

Hi @christianMDU ,

Could you please tell me have you solve your issue now? If so ,please kindly provide the solution and mark your reply. If not, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

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

[SpartaBI]

@christianMDU that means the data type of the column is text.
Change the data type to time in Power Query and close and apply.

Showcase Report – Contoso By SpartaBI

     

[christianMDU]

Hi, thanks for the quick response. I changed the data type to "Time" but forgot to save/apply it. I've gone to redo it but now it's complaining that it cannot automatically convert the column:

[SpartaBI]

@christianMDU can you start from scratch and update me you still get the error?

[christianMDU]

I deleted the measure and restarted Power BI Desktop but am still getting the error.

[SpartaBI]

You do that in power query?

Can you share your model?

[christianMDU]

Sorry, I was doing this in the "Data" panel:

When I open "Transform data", I went into the Transform tab and changed the Data Type to "Time" there:

This seems to have worked, but now I have another problem as the values say "Error":

Is there some sort of maximum figure? It should show 29:41:41 as per the yellow bar.

[SpartaBI]

Don't think it can go above 23:59:59

[christianMDU]

Is there anything I can do to convert that to 1 day, 5 hours, 41 min and 41 sec, for example? 

[daXtreme · Accepted solution]

You can't use the Time datatype. You have to use DURATION.

[christianMDU]

Thanks, that worked. It's giving me an average of "0.45" but Excel says it's 10:13:59 so I need to look at what else I've done wrong. I'm enjoying it though... 🙂

[daXtreme]

You want to take a look at this post: Solved: Purpose of "Duration" datatype in Power Query - Microsoft Power BI Community

[daXtreme]

It's no wonder it says .45 (but I won't go deep into that). I believe you can format a duration datatype using the FORMAT function to turn it into the form [HH]:mm:ss.

[SpartaBI]

@christianMDU ye sure, I'm just not near pc right now, but I hope someone will reply, there are multiple ways to do so.