Can't seem to figure out these averages...the numbers just are not right

[unclejemima]

I've posted on this before, but seem to be getting no-where...the numbers just don't look right.

See example below.

On the left is the sum of the quantity.  On the right, the average of the quantity.  Both are a matrix side by side in power BI on the same page, only difference one is a sum of qty the other average.

You can see the date range is the month of feb.

Using the highlighted example as my problem...if at 10am on saturday I sold a total of 17 qty of the product...there is 4 saturday's in the month of feb, the average should be 17/4= 4.25....not 1.42 as its showing.

I'm not sure what is going on with the average, but it must be averaging the entire table based on day, but not on day and hour???

Can somoene help me with the missing link so I can get a true average sold per hour but based on the average per day.  I'm stumped!!!

Thank you 🙂

[unclejemima]

Would i not need some kind of a calc that would count the days of the week and only divide the average by the days of the week in a date range?

In my example above (in yellow highlighted), I have 4 satuday's in the month so the average so the calc would have to have a helper column with the day of the week split out...then do a count of the number of Saturday's for example in that date and time range for its calculation.

Please help 🙂  I'm stumped and this has been on my list for some time and I can't seem to get it sorted.

[v-jiascu-msft]

Hi @unclejemima,

How did you create the second matrix? Did you use any formulas? It should be a simple visual. Can you share the file please? You can make a copy of your current file and delete the unrelated data. Mask the confidential parts first.

Best Regards,

Dale

[unclejemima]

Second matrix was made simply by changing the value from "Sum" to "Average"

[unclejemima]

Finally got around to doing this.  I'm not sure how to upload so I did a we transfer link.  Link is good for 7 days.

Here is a screenshot of whats in the file.  You'll see the averages numbers are not correct.  I'm not sure why.  Its not averaging per hour, per day...its averaging I think for the day in one go...but i'm wanting to see the average sales per hour per day.

https://wetransfer.com/downloads/c08c4c1660c3b32128c68dab141f4a2a20180403201829/d0f378c8348f7c7bf4d2...

[unclejemima]

Here is a screenshot of whats in the file. 

[unclejemima]

I'm not sure why I'm getting so confused by this...but the number just don't look right.

Use the previous screenshot for example.  The values shown are quantity, not price.

On Saturday, they sell the most of the product.  Total for the month of Feb is 91 sold on Saturday.

The average for Saturday though is only 1.26...less than Friday's average of 1.27...even though on Friday they only sold 57 total what is far less than 91 sold on saturday...so how can the average be less???

Thank you all for your help 🙂