- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Can't seem to figure out these averages...the numb...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

unclejemima

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-19-2018
09:49 PM

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

6 REPLIES 6

unclejemima

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2018
12:10 PM

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

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2018
11:45 PM

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

Community Support Team _ Dale

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

If this post

unclejemima

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-23-2018
03:53 PM

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

unclejemima

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2018
01:23 PM

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.**

unclejemima

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2018
01:25 PM

Here is a screenshot of whats in the file.

unclejemima

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2018
03:46 PM

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