Can anyone explain this difference for Average?

Question

Hello&nbsp; -&nbsp; I am hoping someone can explain how/why Power Bi does this:&nbsp;&nbsp;I have two columns...both with exactly the same information from the same sources. &nbsp;&nbsp; The column on the far right uses the formula below and gives the same value in Excel. &nbsp;&nbsp; - 1.13&nbsp;&nbsp;The column next to it is the same data, but is using the Average option in the field areas. &nbsp; As you can see. &nbsp;&nbsp; But is giving a value of -2.96. &nbsp; &nbsp;&nbsp; Why? &nbsp;&nbsp;Avg of Total Days Diff = CALCULATE(AVERAGE('Orders Table'[Deliv Date Diff]),FILTER('Flu Shipped','Flu Shipped'[Date Shipped]))&nbsp;&nbsp;&nbsp;

parry2k · Answer

Anonymous&nbsp;do you have blank() date shipped.

anonymous · Answer

Hi &nbsp; -&nbsp; No, there are no blank values. &nbsp;&nbsp; There are zeros...but they are meant to be included. &nbsp; &nbsp; A zero indicates the shipment went out the same day it was due. &nbsp;&nbsp; So a due date of Feb 8&nbsp; and an actual ship date of Feb 8&nbsp; = &nbsp; 0 &nbsp; (on time). &nbsp;&nbsp;&nbsp;

parry2k · Answer

Anonymous&nbsp;I think that is getting excluded, may be take few records, 5 or 10 with some zero values and test it manually. 0 also means FALSE and tha'ts why those are getting excluded from the average.
&nbsp;
Would appreciate Kudos 🙂 if my solution helped.

anonymous · Answer

I added a column as per before....the one on the left is a formula (using Average). &nbsp;&nbsp; The far right column is using the "average" option in the field area. &nbsp; &nbsp;&nbsp;If I use the filters to only show values GREATER than 0&nbsp; -&nbsp; then I get a matching average as you can see below. &nbsp;&nbsp;But this is not a true representation of my data...my data has zero values which I need to include. &nbsp; &nbsp; It still seems strange to me that the using the average option would give a different result....one that would not be obvious to the user unless they also had a calculation to compare it to. &nbsp;&nbsp; The only way I knew was because I exported the data into Excel and saw the difference. &nbsp;&nbsp;

parry2k · Answer

Anonymous&nbsp;why you are using filter in your measure. I don't get it.

Forum Discussion

Can anyone explain this difference for Average?

8 Replies

Related content

Average and Moving Average

Weekly Average

Accumulated average

moving average help

Timestamp difference average