Pivot Table / Power Pivot Question - Monthly Average

[mpv99]

I have 500,000 rows of transactional level data. I am summarizing into a pivot (ultimately a pivot chart) by month. I have Slicers for Months and 2 different style differentiations. I would like to keep the monthly sum but also add a Monthly Average (averages the sum of each month) that is dynamic based on the different Slicer options (could be for all selections, a given time period, or style combinations).

I've determined that a Power Pivot may be the only option, but I only understand enough about Power Pivot to suggest it as a solution.

I can't seem to attach the file or screen print

I appreciate any help.

 

[SpillerBD]

Regular PivotTable will handle. Just drop the field again into the values area and then change it from SUM to Average.

 

[mpv99]

Appreciate the help, but that isn't what I'm looking for. I should have been more specific - the transactional data is by date: 1/1, 1/2, 1/3, etc. and then I summarize in the pivot by month.

Creating a separate field creates a daily average (because that is the detail level of the table). I would like the total quantity by month and then an average quantity (average for each monthly sum).

 

[SpillerBD]

You can using Grouping to group dates by month. (Though I can see that gong wrong too so double check those numbers.)
Right click a date label and chose grouping. If your date range crosses years, make sure to include Year as well as Month for the grouping selection.

Some like to have the Years, Month or Quarters as the columns. Since the number of columns is limited for raw dates, begin with dates in the Rows and then apply Grouping then move to the Columns.

 

[SpillerBD]

I can't seem to attach the file or screen print

I appreciate any help.

Check the posting aids on the Forum Guide Lines.
You can use file sharing host like OneDrive, Google Drive and DropBox and post link to the file or send link in private message. Generally do not share macro enabled files but data and macro separate (using txt file) when you have complex macro questions.

 

[SpillerBD]

I've determined that a Power Pivot may be the only option, but I only understand enough about Power Pivot to suggest it as a solution.

If you have PowerPivot available in your version of Excel, then yes that will be the best option. If not, there are ways to stack PivotTables or if your Row/Filter labels. (Check out the PowerBI channel of the forum and PowerPivotPro - Transforming your Business with Power Pivot and Power BI
Slicers, in 2013, can be used with Tables and could be used in conjunction with SUBTOTAL functions to correctly calculate the averages you are looking for. It would be a shame if the slices cannot be linked to both the Pivot Table and a Table at the same time. I haven't used/tested that possibility.