How to get Average if Data Type is is "Text"

[Anonymous]

Hello Everyone!

I'm having a hard time to get the average of Turn Around Time (TAT) that is in the "Text Format"

my TAT format is like this "hh:mm:ss" and once i visualize this using CARD, there's no option to make it as "Average"

Note: there is no "Average" option in "Fields" that's

TYIA!

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can creata a measure

Average_seconds = AVERAGEX('Table (2)',LEFT([Column1],2)*3600+MID([Column1],4,2)*60+RIGHT([Column1],2))

Then put the measure to the card visual

Best Regards!

Yolo Zhu

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

[amitchandak]

@Anonymous , You have first create a number out it take avg and then again convert to text

Value = averagex( Table, (left([Col],2) *3600) + (mid([Col],4,2) *60) + (right([Col],2) *3600)  )

then you can convert to text again 

quotient([Value],3600) & ":" & quotient(mod([Value],3600),60) & ":" mod(mod([Value],3600),60)

Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer