Average TIME Calculation differs between Excel and PowerBI

Question

Before i go into the details, here is the sample data that i am working on.

this is swipecard data and it contains all the in's and out's of users..

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

I am trying to calculate the spent hours of each users by calculating their differences between

First In(MIN) and Last Out(MAX)

I have used below dax Calculated column for the Min InTime and MaxOutTime.

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 have calculated the spent hours by using 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 is giving me below average value which is mismatching with powerbi 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.

Please correct me.

Thanks.

For average per what are you trying to calculate? Of course the average will be the same as spend hours because you're putting them in the same context, which means that the average is just the average of the one row it's in. — Foxan Ng
@FoxanNg I am trying to calculate the average spent hours of employees. if you see the overall average value that is. By dax it is 13.13 and in Excel it is 11:45...Thats what i am worried about. Can you please correct me... — Bunny

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-09T15:51:32

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.

Average TIME Calculation differs between Excel and PowerBI

1 Answers