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
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.
Hi Kelly,
Please helpp on this.
Please find the attched source and PbIX and help me to find the average time.
REgards,
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