Average TIME Calculation differs between Excel and PowerBI

Question

Here is a sampling of the data that I am working with before I get into further detail. All of the users' ins and outs are contained in this swipecard data.

https://1drv.ms/x/s!AhiQ2f7YQHC-gbNYMGJz0l0KU70svg

By computing the disparities between each user's total hours spent and their total hours spent, MIN (first in) and LO (last out) (MAX)

For the Min InTime and MaxOutTime, I used the dax calculated column in the accompanying table.

MinInTIME = CALCULATE(
  MIN('BaseData'[Date-Time]),
      FILTER(ALLEXCEPT('BaseData','BaseData'[EMP_ID],'BaseData'[Begin_Date]),'BaseData'[Type]="IN"))

OutTime

MAXOutTIME = CALCULATE(
  MAX('BaseData'[Date-Time]),
      FILTER(ALLEXCEPT('BaseData','BaseData'[EMP_ID],'BaseData'[Begin_Date]),'BaseData'[Type]="OUT"))

Then I calculated the spent hours by using the below Dax.

Spent Hours = SWITCH(TRUE(),
'BaseData'[MAXOutTime]=BLANK() && 'BaseData'[MinInTIME]='BaseData'[MinInTIME],BLANK(),
'BaseData'[MinInTIME]=BLANK() &&  'BaseData'[MAXOutTime]= 'BaseData'[MAXOutTime],BLANK(),
'BaseData'[MAXOutTime] < 'BaseData'[MinInTIME],BLANK(),
'BaseData'[MinInTIME]='BaseData'[MinInTIME] &&  'BaseData'[MAXOutTime]= 'BaseData'[MAXOutTime],FORMAT( 'BaseData'[MAXOutTime]-'BaseData'[MinInTIME],"HH:MM"))

I got the right values. But when I tried to calculate the average using below dax, then the issue occurs.

AvgSpendHours = FORMAT(AVERAGE('BaseData'[Spent Hours]),"HH:MM")

Output:-

I exported it into excel and checked the values Then it gives me below the average value which is mismatching with power bi Dax average value

which is, in Dax it is 13:13 and in excel it is 11:45

Is it with my DAX or PowerBI or with Excel?

Answer 1

They are calculating different things. In Power BI, you are taking the average OUT minus IN time for each pair. In Excel, you are taking the average of the daily average OUT minus IN.

---

Consider a simplified example over just two days:

 IN        |  OUT        | Spend Hours
-----------|-------------|------------
9AM 1/1/18 |  5PM 1/1/18 | 8
8AM 1/1/18 |  2PM 1/1/18 | 6
9AM 1/2/18 | 10PM 1/2/18 | 13

On 1/1/18, there are two ins and outs with a 7-hour average for that day. When you average that 7-hour average with the 13-hour average from 1/2/18, your Excel average per day is 10 hours. However, if you average over each row, you get the (8+6+13)/3 = 9-hour Power BI average per in/out pair.