Josh_excel
New Member
- Joined
- Feb 13, 2016
- Messages
- 1
Using a very helpful method found here:
http://www.contextures.com/pivottablecountunique.html#download
I've successfully obtained 'distinct' value counts in Excel 2010 by making a Pivot Table of another Pivot Table.
The final Pivot Table's Row Labels are 30-minute increments of time (8:00, 8:30, 9:00, etc) and the Column Labels are site locations of stores owned by the company for which I work.
The Values are the number of employees working (these are the distinct values, obtained from a tabular data set with an employee field as a column).
The source data spans a month (January 2016) and I've got slicers in the Pivot Table to scroll through each date, and individual weekdays, and those are helpful.
With Conditional Formatting, the Pivot Table is a heat map of how many employees are working in each half-hour at each store.
But to get to the final step, I need some help!
Instead of this final Pivot Table showing values for one date at a time, I would like it to show an average of the values, over each days/dates which are selected by the slicers.
For example, right now the Pivot Table shows us that 5 employees were working at Store #4 at 09:30am on 1/8/2016, and how many employees are working during each half-hour increment for each store, etc.
But ideally, I would like the Pivot Table to show us that an average of 5.3 employees worked at Store #4 at 09:30am on weekdays in January.
Is there a way to display this data as an average over the dates?
I've looked at the "Show Values As" option in the Calculations section of the Options Ribbon under Pivot Table Tools, but since the Dates are not a Column Label or Row Label in this Pivot Table, I'm not sure if it's possible.
Perhaps what I need is a Calculated Field, but I'm not quite sure how to set that up in this situation.
Thanks for your time and help!
http://www.contextures.com/pivottablecountunique.html#download
I've successfully obtained 'distinct' value counts in Excel 2010 by making a Pivot Table of another Pivot Table.
The final Pivot Table's Row Labels are 30-minute increments of time (8:00, 8:30, 9:00, etc) and the Column Labels are site locations of stores owned by the company for which I work.
The Values are the number of employees working (these are the distinct values, obtained from a tabular data set with an employee field as a column).
The source data spans a month (January 2016) and I've got slicers in the Pivot Table to scroll through each date, and individual weekdays, and those are helpful.
With Conditional Formatting, the Pivot Table is a heat map of how many employees are working in each half-hour at each store.
But to get to the final step, I need some help!
Instead of this final Pivot Table showing values for one date at a time, I would like it to show an average of the values, over each days/dates which are selected by the slicers.
For example, right now the Pivot Table shows us that 5 employees were working at Store #4 at 09:30am on 1/8/2016, and how many employees are working during each half-hour increment for each store, etc.
But ideally, I would like the Pivot Table to show us that an average of 5.3 employees worked at Store #4 at 09:30am on weekdays in January.
Is there a way to display this data as an average over the dates?
I've looked at the "Show Values As" option in the Calculations section of the Options Ribbon under Pivot Table Tools, but since the Dates are not a Column Label or Row Label in this Pivot Table, I'm not sure if it's possible.
Perhaps what I need is a Calculated Field, but I'm not quite sure how to set that up in this situation.
Thanks for your time and help!
