Average qty sold per working days

[unclejemima]

I've got a table called "TranData"" what includes a Inventory Part, a Quantity and a Date.

I'm trying to build an average of Quanity sold for only working days.  The business is open Mon-Sat, closed on Sunday.  I'm looking then to get averages of Quantity sold Per Monday, Per Tuesday, etc.  So I can get a idea of what is the true average of each specific day, example on Monday's over time we sold this product X many times.

Looking for advice 🙂

[Greg_Deckler]

Check out my quick measure for Net Work Days in the Quick Measure gallery. You should just have to tweak it as mine excludes Sat and Sun.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[unclejemima]

This looks good thanks, but I think I might be explaining this wrong 🙂  Let me try again...

I'm able to build this, this is sample data for one month that shows the sales of a specific product per hour for each day of the week.

This is straight correct for the month of Feb 2018. and works good for showing the TOTAL sales, set per hour, per day of the week.

Problem comes when I try and avearge the numbers per hour based on the day of the week.

I can change this to "Average" but the numbers are all out of whack.

Use 11 Am for example.  If I count in Feb 2018, looking at there are 4 saturdays in a month, there were 17 of the product sold at 10am on Saturday, so it should average to 4.25...but instead its saying 1.42.

 

I'm not sure why the average option (instead of sum) is not working, and I assume I need to make a calc column to average each day of the week?

Example, Monday = if (trandata[date] = Monday, then...? and I'd have to do it by day of the week?

There must be a logical way for this that is easy to do to get daily average based on the info I have?  I'm so close 😉

[unclejemima]

Anyone?  Do you guys need more screenshots/detail to help with a solution?

[Greg_Deckler]

Going to need some sample/example data for this one to see what is going on, too difficult to understand what might be happening. If you can share the PBIX file, great, otherwise:

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

[unclejemima]

Here is some example data. 

Hope this works. I can't post the power bi file because its private info.  Wish I could...would make it much easier 🙂

This shows basically the raw data I have...

What I'm trying to do is get this...

[Newbie1]

Hi

Please use Matrix visualization and put hours as row and day as column. Qty would be put in values. Click on Qty and select average. It will do what you want to achieve.

I hope it helps

[unclejemima]

Your talking instead of doing SUM of quantity, for it to do AVERAGE of quantity, correct?

I tried this and the numbers it gives me are not the averages per day/hour...it looks almost as if its averaging against everything (all hours, all days average)

Could I create a new column or measure that would figure the average per day?

[Newbie1]

Are you sure about it. Please see input data and output and check if you are doing the same.

Regards

[unclejemima]

Newbie1, did you change your qtys in your example to average to see if it gave wonky numbers for the averages like mine is???

[Newbie1]

They are averages. The formula is (Total quantity sold during certain hours on a particular day/Frequency of days). For eg. if at 11.00, 6 items were sold on Friday and at 11.00 4 items were sold next Friday. Then, average would be (6+4)/2=5.

Hope it helps.

[unclejemima]

Your numbers don't look like averages...can you set to average and see what your numbers look like?

-mark

[unclejemima]

Here is what it looks like with the raw data...as "sum" its perfect...but if i change quanity to "average" the average looks very wrong.