• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advice on a Pivot Table

S

seansr

Member
I have a regular export of data and I want to be able to show the hourly sales (which I have done) but I also want to show the Average over the week


Also I want to sort by calandar week Mon-Sun


So in example 1st march will be in 1 week but the 4-10 with be in the 2nd week and so on


I have tried, and this is my first attemp of pivot tables, but if I try and get a average I get an average for all enteries. If I find the sort by week I get the first 7 days / second 7 days ect


Is what I want possible, and if so can you help

Copy of data available

https://www.dropbox.com/s/begj9asnuq6pamf/Hourly%20Sales%20Pivot%20Table%20.xlsx


Sean
 
Hi Seansr,


Can you try following:


After data add a helper column with formula WEEKNUM(F3) and drag down this will give you the weeks of all the dates.


Now re-draw the pivot table, with following:


1. Add Tracking Hour to column Labels.

2. Add Weeknum and Trading Area to the Row Label.

3. Add Gross Sales to the Values and change it to average.


Hopefully you will get the week-wise average, i will upload file from home.


Regards,

Faseeh
 
I have added the week number - Great

I have managed to add a subtotal, and average it but this gives me an average per transaction, not average per hour


Sean
 
Hi seansr,


Please see this file and advise:


https://dl.dropboxusercontent.com/u/60644346/Hourly%20Sales%20Pivot%20Table%20.xlsx


Faseeh
 
Faseeh


This still gives me average tranaction not average / hour


Udated version is Filtered by site

Shows the hours

Average / transaction


Is it possible to make it show average per Hour/ over the week ie

58.65

57.80

76.60

91.25

46.85

------

66.23 not 2.85


https://www.dropbox.com/s/begj9asnuq6pamf/Hourly%20Sales%20Pivot%20Table%20.xlsx


If that's possible that would be perfect
 
Hi Seansr!


Please check It I hope it will solve your problem


https://www.dropbox.com/s/vx9fekz150u7q6m/Hourly%20Sales%20Pivot%20Table%20.xlsx?v=0scn
 
Hi Seansr!


I have tried and found there are two solution:


1. Either you use an extra column outside the Pivot Table to averge things.

2. You can go for power pivot to solve the issue.


See this:


http://www.mrexcel.com/forum/powerpivot-questions/625617-grand-total-average-sum-sales-per-month.html


...Which ever you like.


Regards,
 
You must log in or register to reply here.
Back
Top