topic Re: Calculating difference between two time values column and finding its average in DAX Commands and Tips

Calculating difference between two time values column and finding its average

Anonymous — Tue, 18 Feb 2020 16:53:08 GMT

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

Re: Calculating difference between two time values column and finding its average

Pragati11 — Tue, 18 Feb 2020 17:01:23 GMT

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

Re: Calculating difference between two time values column and finding its average

az38 — Tue, 18 Feb 2020 17:07:41 GMT

Hi Anonymous

try a measure

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

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

depends on business logic

Re: Calculating difference between two time values column and finding its average

Anonymous — Sat, 22 Feb 2020 11:50:42 GMT

Hi az38

Thank you for your help. It worked for me.

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

Re: Calculating difference between two time values column and finding its average

az38 — Sat, 22 Feb 2020 11:56:12 GMT

Anonymous

anytime. new threads, private messages, Linkedin.

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

Re: Calculating difference between two time values column and finding its average

Anonymous — Sat, 22 Feb 2020 12:50:09 GMT

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

Re: Calculating difference between two time values column and finding its average

az38 — Sat, 22 Feb 2020 13:57:55 GMT

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

Re: Calculating difference between two time values column and finding its average

Anonymous — Sat, 22 Feb 2020 21:08:52 GMT

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

Re: Calculating difference between two time values column and finding its average

az38 — Sat, 22 Feb 2020 21:43:14 GMT

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)