topic Re: AVERAGE measure produce error in DAX Commands and Tips

AVERAGE measure produce error

Anonymous — Tue, 17 May 2022 12:44:13 GMT

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:

Re: AVERAGE measure produce error

SpartaBI — Tue, 17 May 2022 12:48:27 GMT

Anonymous 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

Re: AVERAGE measure produce error

Anonymous — Tue, 17 May 2022 12:55:39 GMT

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:

Re: AVERAGE measure produce error

SpartaBI — Tue, 17 May 2022 12:59:17 GMT

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

Re: AVERAGE measure produce error

Anonymous — Tue, 17 May 2022 13:00:06 GMT

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

Re: AVERAGE measure produce error

SpartaBI — Tue, 17 May 2022 13:08:19 GMT

You do that in power query?

Can you share your model?

Re: AVERAGE measure produce error

Anonymous — Tue, 17 May 2022 13:18:38 GMT

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.

Re: AVERAGE measure produce error

SpartaBI — Tue, 17 May 2022 13:21:45 GMT

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

Re: AVERAGE measure produce error

Anonymous — Tue, 17 May 2022 14:27:17 GMT

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

Re: AVERAGE measure produce error

SpartaBI — Tue, 17 May 2022 14:32:15 GMT

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

Re: AVERAGE measure produce error

daXtreme — Tue, 17 May 2022 14:40:42 GMT

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

Re: AVERAGE measure produce error

Anonymous — Tue, 17 May 2022 15:42:12 GMT

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... 🙂

Re: AVERAGE measure produce error

daXtreme — Tue, 17 May 2022 15:48:04 GMT

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.

Re: AVERAGE measure produce error

daXtreme — Tue, 17 May 2022 15:56:09 GMT

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

Re: AVERAGE measure produce error

Anonymous — Fri, 20 May 2022 05:52:35 GMT

Hi Anonymous ,

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.