Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculating difference between two time values column and finding its average

 

 

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:

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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")
 
3.png
 
 
 
 
 
 
 
 
 
 
 
 
Now instead of getting an average of the column "Avg store reach time" I am getting the below error:
 
5.png
 
Please help me with this. Thank you
RK
1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

8 REPLIES 8
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Hi @az38 

 

Thank you for your help. It worked for me.

 

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

az38
Community Champion
Community Champion

@Anonymous 

anytime. new threads, private messages, Linkedin.

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@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())
 
1.png
 
 
 
 
 
 
 
 
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
Community Champion
Community Champion

@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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@az38 

 

Actually I want total orders done by only these riders:

 

1.png

 

 

 

 

 

 

 

 

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
Community Champion
Community Champion

@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)

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.