Calculating difference between two time values column and finding its average

[Anonymous]

Hi Everyone,

I need your help to solve a problem that I am facing.

I have two columns which have time based value as shown in the picture:

I found their difference using the formula    Duration.ToText([Reached Merchant]-[Assigned At])), I got the result as follows:

Now in order to find average of "Avg store reach time", I made a new measure as follows:

Measure = FORMAT(AVERAGE(Sheet1[Avg store reach time]),"HH:MM:SS")

Now instead of getting an average of the column "Avg store reach time" I am getting the below error:

Please help me with this. Thank you

RK

[az38 · Accepted solution]

Hi @Anonymous 

try a measure

Measure = format(calculate(averagex('Table',TIMEVALUE('Table'[Avg store reach time]))),"HH:MM:SS")

or

Measure = format(averagex('Table',TIMEVALUE('Table'[Avg store reach time])),"HH:MM:SS")

depends on business logic

[Anonymous]

Hi @az38 

Thank you for your help. It worked for me.

Is it ok if I can contact you for any future help?

[az38]

@Anonymous 

anytime. new threads, private messages, Linkedin.

the same about the Community - there are a lot of great users passionate to help you

[Anonymous]

@az38 

Thank you so much for the support.

Actually I am a little new to Power BI and hence facing another problem.

I used the below measure to find the top 5 riders:

Top 5 Riders = VAR RiderRank = (RANKX(ALL(Sheet1[Rider]), [Out The Door Time], ,DESC)) RETURN IF( RiderRank <=5, [Out The Door Time], BLANK())

Now I want a measure by which I can find order count done by only these riders. Now order count in a different measure:

Count Order = Count(Sheet1[OID])

I am not knowing which DAX to use. Please help @az38 

Thank you

[az38]

@Anonymous 

if I understand you correct you need a measure like 

C = calculate(countrows(Sheet1), ALLEXCEPT(Sheet1, Sheet1[Rider]))

it will give you a countrows for each rider independ on his rank, so you can display it in visual without any additional actions from your side

[Anonymous]

@az38 

Actually I want total orders done by only these riders:

Now counting the number of orders done by each of these 5 guys only is what I want.

Count of orders is a seperate measure:

Count Order = Count(Sheet1[OID])

So I want to know total orders done by only those 5 riders using above measure.

Hope I am able to convey my query

Thanks

[az38]

@Anonymous
You can just create a Riderrank column as you did it above
Then create a measure
measure = calculate(countrows(Sheet1), all(Sheet1), RiderRank<6)

[Pragati11]

Hi @Anonymous ,

"Avg Store reach time" is of STRING datatype because your calculation as below returns a text type of value:

Duration.ToText([Reached Merchant]-[Assigned At]))

Thanks,

Pragati