Forum Discussion
dax
Hi Anonymous ，
You need to create 3 calculated columns to get the value of “HH”,”MM”and “SS” first, the related dax expressions are listed below:
HH = IF('Sheet1'[Position 1]=0,BLANK(),LEFT('Sheet1'[Resolution Time (H:M:S)],'Sheet1'[Position 1]-1))MM = IF('Sheet1'[Position 1]=0||'Sheet1'[Position 2]=0,"0", MID('Sheet1'[Resolution Time (H:M:S)],'Sheet1'[Position 1]+1,'Sheet1'[Position 2]-('Sheet1'[Position 1]+1)))SS = RIGHT('Sheet1'[Resolution Time (H:M:S)],LEN('Sheet1'[Resolution Time (H:M:S)])-'Sheet1'[Position 2])Finally,create a measure to get the value of average:
measure = AVERAGE(Sheet1[Minutes])For the related .pbix file, pls click here.
Best Regards,
Kelly
Hello Kelly,
Trying the same but getting this error pls help, when trying LEN & Left independently its working
Regards.
Hi Anonymous ，
Left function is used for a text type data, be sure that the column of “Response time (H:M:S)” is a text type data, not a date type.
If the column Response time (H:M:S) is a date type, please create a calculated column below:
Minutes =
var a =FORMAT('Table (2)'[Date2],"HH")
var b =FORMAT('Table (2)'[Date2],"mm")
var c=FORMAT('Table (2)'[Date2],"ss")
return
a*60+b+c/60
Thanks.
Best Regards,
Kelly
Hi Kelly,
Thanks for your time. ideally, i wish to calcualte the Avearge in HH:MM;SS format.
Ist possible?
Hi Anonymous ,
What you need is to create a measure as below:
Average = AVERAGE('Table'[Minutes])For the related .pbix file, you can turn to the URL below:
Hope this would help.
Best Regards,
Kelly
Hi Kelly,
The Data shows as text but could not able to use the same. may i know why a new column with"Minute" has been created in the attachment?
Does it has any importance?
If u wish i can attach the Pbx.
Regards.