Getting 3 different averages from Power BI, and neither match the average from Excel.

[Anonymous]

Hello,

Note: I am still very new to Power BI and writing DAX so any help is appreciated!

So I'm looking at pick times for orders. The excel report that I'm running has 50000 slots, but only about 23,000 are actuall filled which leaves me with a lot of 0's. When I highlight the column 'Total Pick Seconds', with the 0's filtered out, to have Excel give me the average of 213 seconds.

When I import the data into Power BI, I edit the PICK_ID query to filter out any PICK_ID that is a zero since it's technically empty.

I then made a column

DPT_PICK_TIME = (AVERAGE(Table1[Total Pick Seconds]) which spat out the average as 222.47, but unfortunately it would give me the save average for every single day being 222.47. This is on par when I make a card with Average of 'Total Pick Seconds'

I then rewrote the column to be 

DPT_PICK_TIME = CALCULATE(AVERAGE(Table1[Total Pick Seconds]), ALLEXCEPT(Table1,Table1[DAY])) to be able to see different days averages. The overall average this gave me was 258.79, but also allowed me to view the average times of different days. The reason I did this was because I used a similar DAX line to view specific user's averages. These averages matched what Excel gave me so I figured it would do the same. For example, when I use Excel to view the pick times on Saturday it matches the average time that Power BI gives me. The user averages also match so I was inclined to believe this would work.

Then, when I actually made a bar graph, I added the average line to the visual and this line is saying that the average for 'Total Pick Seconds' is actually 237.

I'm inclined to believe that the real average is the one that excel gives me, but I'm confused as to what I'm doing in Power BI to give me such different numbers.

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[amitchandak · Accepted solution]

I did not get it completely. But how you take Average is will decide you Average.

So if take

DPT_PICK_TIME = (AVERAGE(Table1[Total Pick Seconds])

It is simple Avg of all rows

DPT_PICK_TIME = CALCULATE(AVERAGE(Table1[Total Pick Seconds]), ALLEXCEPT(Table1,Table1[DAY]))

It will take avg at day level and then use that in final average

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490