Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:                             ...
  • az38's avatar
    az38
    6 years ago

    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

az38's avatar
az38
Community Champion
6 years ago

Anonymous 

anytime. new threads, private messages, Linkedin.

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        az38
        Community Champion
        6 years ago
        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)