title: Average TIME Calculation differs between Excel and PowerBI
body: <p>Before i go into the details, here is the sample data that i am working on.</p>
  
  <p>this is swipecard data and it contains all the in's and out's of users..</p>
  
  <p><a href="https://1drv.ms/x/s!AhiQ2f7YQHC-gbNYMGJz0l0KU70svg" rel="nofollow noreferrer">https://1drv.ms/x/s!AhiQ2f7YQHC-gbNYMGJz0l0KU70svg</a></p>
  
  <p>I am trying to calculate the spent hours of each users by calculating their differences between </p>
  
  <p>First In(MIN) and Last Out(MAX)</p>
  
  <p>I have used below dax Calculated column for the Min InTime and MaxOutTime.</p>
  
  <pre><code>MinInTIME = CALCULATE(
    MIN('BaseData'[Date-Time]),
        FILTER(ALLEXCEPT('BaseData','BaseData'[EMP_ID],'BaseData'[Begin_Date]),'BaseData'[Type]="IN"))
  </code></pre>
  
  <p>OutTime</p>
  
  <pre><code>MAXOutTIME = CALCULATE(
    MAX('BaseData'[Date-Time]),
        FILTER(ALLEXCEPT('BaseData','BaseData'[EMP_ID],'BaseData'[Begin_Date]),'BaseData'[Type]="OUT"))
  </code></pre>
  
  <p>Then i have calculated the spent hours by using below dax.</p>
  
  <pre><code>Spent Hours = SWITCH(TRUE(),
  'BaseData'[MAXOutTime]=BLANK() &amp;&amp; 'BaseData'[MinInTIME]='BaseData'[MinInTIME],BLANK(),
  'BaseData'[MinInTIME]=BLANK() &amp;&amp;  'BaseData'[MAXOutTime]= 'BaseData'[MAXOutTime],BLANK(),
  'BaseData'[MAXOutTime] &lt; 'BaseData'[MinInTIME],BLANK(),
  'BaseData'[MinInTIME]='BaseData'[MinInTIME] &amp;&amp;  'BaseData'[MAXOutTime]= 'BaseData'[MAXOutTime],FORMAT( 'BaseData'[MAXOutTime]-'BaseData'[MinInTIME],"HH:MM"))
  </code></pre>
  
  <p>I got the right values. But when i tried to calculate the average using below dax, then the issue occurs.</p>
  
  <pre><code>AvgSpendHours = FORMAT(AVERAGE('BaseData'[Spent Hours]),"HH:MM")
  </code></pre>
  
  <p>Output:-</p>
  
  <p><a href="https://i.stack.imgur.com/rZPw8.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rZPw8.jpg" alt="enter image description here"></a></p>
  
  <p>I exported it into excel and checked the values </p>
  
  <p>then it is giving me below average value which is mismatching with powerbi dax average value </p>
  
  <p>which is, in dax it is 13:13 and in excel it is 11:45</p>
  
  <p><a href="https://i.stack.imgur.com/kvjMm.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kvjMm.jpg" alt="enter image description here"></a></p>
  
  <p>Is it with my DAX or PowerBI or with Excel.</p>
  
  <p>Please correct me.</p>
  
  <p>Thanks.</p>