Average TIME Calculation differs between Excel and PowerBI

0 votes

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:-

enter image description here

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

enter image description here

Is it with my DAX or PowerBI or with Excel?

Sep 20 in Others by Kithuzzz
• 12,240 points
21 views

1 answer to this question.

0 votes

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.

answered Sep 21 by narikkadan
• 20,880 points

Related Questions In Others

+1 vote
1 answer
0 votes
1 answer

What is the difference between hadoop and google analytics ?

I will try and answer this as ...READ MORE

answered Aug 22, 2018 in Others by Frankie
• 9,830 points
822 views
0 votes
1 answer
0 votes
0 answers

Mention the difference between Data Driven Testing and Retesting?

Retesting:  It is a process of checking ...READ MORE

Feb 1, 2019 in Others by riya

closed Feb 1, 2019 by Omkar 1,583 views
0 votes
1 answer

Using Multiple filters in DAX

CALCULATE is defined as CALCULATE(<expression>,<filter1>,<filter2>…) This means that you can ...READ MORE

answered Dec 22, 2020 in Power BI by Gitika
• 65,890 points
3,651 views
+3 votes
2 answers

Combine tables in Power BI

You can also achieve this using a ...READ MORE

answered Oct 5, 2018 in Power BI by lina
• 8,220 points

edited Oct 11, 2018 by Kalgi 1,355 views
0 votes
1 answer

Power Bi Dax Table

You need to wrap the numbers in ...READ MORE

answered Oct 5, 2018 in Power BI by Kalgi
• 52,350 points
500 views
0 votes
2 answers

Power BI Dax Multiple IF AND Statements

HI I have Column Patient 12 13 14 15 18 IN Patient=12, 13 Out ...READ MORE

answered May 23, 2019 in Power BI by VNK
17,443 views
0 votes
1 answer

Differentiate between Tokyo and Seoul Time zone

if DST and Offset are the only ...READ MORE

answered Feb 11 in Others by narikkadan
• 20,880 points
54 views
0 votes
1 answer

Difference between <meta name="title"> tag and <title></title> tag

<title> This will be displayed in the ...READ MORE

answered Feb 26 in Others by narikkadan
• 20,880 points
173 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP