cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Calculate the median of Time Column

Hi, 

 

I am wanting to calculate the median of a column that has time values. And I want it to be formatting like hh:mm:ss.

 

Column Example:

 

Time to Contact

00:11:04

00:21:32

00:13:54

00:12:43

 

I am using the measure:

= FORMAT(MEDIAN(Table1[Time to Contact]), "HH:MMSmiley FrustratedS")

 

I am geting the error "MEDIAN does not support expressions of type string/Boolean/date

 

Any help?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RachC Regular Visitor
Regular Visitor

Re: Calculate the median of Time Column

only two step to solve this situation.

 

first) set new column to set date to second(like unix timestamp)

 

tosecond = TIMEVALUE(TABLE1[timeconcat]) *1螢幕快照 2017-05-11 上午11.54.40.png

 

 

second)set measure to median your second and convert back to time with DAX FORMAT 

 

 

measure = FORMAT(MEDIAN(TABLE1[tosecond]),"hh:mm:ss")螢幕快照 2017-05-11 上午11.55.10.png

 

I hope it could help you.

6 REPLIES 6
scottsen Senior Member
Senior Member

Re: Calculate the median of Time Column

Times can be cast into floating point #'s... (each increment by 1.0 is 1 full 24-hour day).

 

I want to say VALUE( ) is the function to use for casting?

Re: Calculate the median of Time Column

Thanks! 

 

How would I structure the new measure?

scottsen Senior Member
Senior Member

Re: Calculate the median of Time Column

I suspect if just create a new calc column where your time is a floating point number... your above measure will "just work"

RachC Regular Visitor
Regular Visitor

Re: Calculate the median of Time Column

only two step to solve this situation.

 

first) set new column to set date to second(like unix timestamp)

 

tosecond = TIMEVALUE(TABLE1[timeconcat]) *1螢幕快照 2017-05-11 上午11.54.40.png

 

 

second)set measure to median your second and convert back to time with DAX FORMAT 

 

 

measure = FORMAT(MEDIAN(TABLE1[tosecond]),"hh:mm:ss")螢幕快照 2017-05-11 上午11.55.10.png

 

I hope it could help you.

Re: Calculate the median of Time Column

Thank You!!

RachC Regular Visitor
Regular Visitor

Re: Calculate the median of Time Column

if you works well,pls give a thumb(umm kudo?) for me,thks.

 

send me message if you me other problem.