CODEWITHSUNDEEP
excelpivot-table
Richie
Richie

Reputation: 273

Is what I need two PivotTables for possible with just one?

I have a spreadsheet with data similar to this:

Ticket,    Date,   Priority,  Duration  
1,        1/1/2013,    2,        120  
2,        2/6/2013,    3,         40  
3,       2/10/2013,    1,         90  
4,        3/7/2013,    3,        100  
5,       5/24/2013,    4,        200  
6,       5/31/2013,    1,        190  
7,       6/10/2013,    2,        200

and I would like to create a pivot table to show the number of tickets by priority by month and the average duration by month, like so:

Month High Medium Urgent Low Average  
Jan    1     0      0     0     120  
Feb    0     2      3     0      65  
Mar    0     4      0     0     100  
May    0     0      6     5     195  
Jun    7     0      0     0     200

I can only manage to do this by using two pivot tables however I am interested to know if it can be done by using just a single pivot table.

Upvotes: 0

Views: 64

Answers (1)

pnuts
pnuts

Reputation: 59485

In one PT:

SO21372316 example

This is with:

  1. grouping of date by month
  2. hiding some columns
  3. manual addition of 'Month' and priorities in PT
  4. manually reordering the priorities
  5. setting the PivotTable Options..., Format, For empty cells show: to 0
  6. Totals & Filters, Grand Totals, Show grand totals for rows checked and Show grand totals for columns unchecked.

Upvotes: 1

Related Questions