Forum Discussion

bgepps's avatar
bgepps
Frequent Visitor
9 years ago
Solved

Create a column to calculate average time difference

Hi guys,   I am struggling with a problem calculating average days/times in Power BI Desktop.   I have the following sets of data;   Job status Status start date (date/hh/mm/ss) Status end d...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi bgepps,

     

    Please see the screenshot below, click the table visualization, select the Status, Diff and Average measure as Values level. You will get the expected result.

     


    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi bgepps,

 

Based on your description, I try to create sample data and reproduce your scenario, I get the expected result as follows.

 

Create sample data.


 


Create calculate column to get the time intervals spent for each status.

 

Diff = DATEDIFF(Table5[Start Date],Table5[End Date],HOUR)


 

Create a measure to calculate the average time spent on each job status. Create table to show the result.

Average = DIVIDE(CALCULATE(SUM(Table5[Diff]),ALL(Table5)),CALCULATE(DISTINCTCOUNT(Table5[Status]),ALL(Table5[Status])))


 
If this is what you want, please share the sample data for further analysis.


Best Regards,
Angelia

  • bgepps's avatar
    bgepps
    Frequent Visitor
    9 years ago

    Hi Angelia,

     

    Thanks for your help with this, it looks promising so far.

     

    Once i have added the measure, how do i produce the table like your example below? Apologies, bit of a BI beginner!

     

    Appreciate your help,

     

    Ben

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi bgepps,

       

      Please see the screenshot below, click the table visualization, select the Status, Diff and Average measure as Values level. You will get the expected result.

       


      Best Regards,
      Angelia