Forum Discussion
Average call time measure
Hi everyone
I have created a measure for average call time but it is only available as a tooltip rather than being able to use it as values. Any ideas?
Hi AmiA
The thing is that there is no duration data type in Power BI report. When you use average('table'[call time]), it doesn't return the correct average duration of calls if call time is of time data type. You need to do some calculation to convert call time to numeric data type with unit hour, minute or second, then calculate the average of them. After that, format it into what you like.
Here is Greg's blog that may help: Aggregating Duration/Time - Microsoft Power BI Community
Another thing is that when you format the duration time like "hh:mm:ss", it will be changed into text data type while text data type field cannot be put into some visuals as Values because it cannot be used in some calculations. If you put it in visuals like table/card/matrix... which can accept text data, it would be ok. If you put it in visuals like column chart/line chart... that compares the size of data, it would be not possible. You need to remove its format to keep it in numeric data type so that it can be used in these visuals.
I know this is not very ideal but I cannot find perfect solution to it currently. Please vote for this idea: Microsoft Idea · field of "duration" type
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
7 Replies
- daxer-almighty
Solution Sage
Average call time should be time or a number, not text, in order to be able to use it in calculations.
- daxer-almighty
"Average call time should be time or a number, not text, in order to be able to use it in calculations."
Did I say anything about the call time column?
Sorry, I may have misunderstood your response. Fairly new PowerBi user over here. How do I make average call time show as time or number? I assumed it would automatically set to that if the column it was based on was set to time.
v-jingzhangCommunity Support
Hi AmiA
When you use format function in this case, it will return a text type value rather than a number type value or a time type value. That is the cause.
According to your purpose, I guess your call time column represents a duration of a call rather than a time point when a call started or ended, right? If so, to calculate the average call time, you need to notice that in Power BI Desktop, the Time data type refers to a time point rather than a duration of time.
Meanwhile, which visual did you hope to put the measure in? I guess you put it into a visual that only accepts number values, so you met the issue.
Let me know more details about above questions so that I can suggest what to do further.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Hi v-jingzhang
Yes, the call time in this instance is the length of a call rather than the time the call was.
I've tried this in a number of visuals and am getting the same results.
What i am trying to achieve is the average call time month on month.
- v-jingzhang
Hi AmiA
The thing is that there is no duration data type in Power BI report. When you use average('table'[call time]), it doesn't return the correct average duration of calls if call time is of time data type. You need to do some calculation to convert call time to numeric data type with unit hour, minute or second, then calculate the average of them. After that, format it into what you like.
Here is Greg's blog that may help: Aggregating Duration/Time - Microsoft Power BI Community
Another thing is that when you format the duration time like "hh:mm:ss", it will be changed into text data type while text data type field cannot be put into some visuals as Values because it cannot be used in some calculations. If you put it in visuals like table/card/matrix... which can accept text data, it would be ok. If you put it in visuals like column chart/line chart... that compares the size of data, it would be not possible. You need to remove its format to keep it in numeric data type so that it can be used in these visuals.
I know this is not very ideal but I cannot find perfect solution to it currently. Please vote for this idea: Microsoft Idea · field of "duration" type
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
