pivot table define layout and totals differently per value field?

SvenBlomme

New Member
Joined
Nov 28, 2011
Messages
13
Hello,I am working for a healthcare service provider. For keeping track of our actual time spent versus budgeted time, I would like to create a pivot table which contains this information. In this table, I would like to display the actual time spent on our tasks, in columns per month, with the grand total at the end of each row (in the far right of the table). However, I would like to display the budgeted time next to these actual times, but ONLY the totals, not the breakdown per month. Is it at all feasible to do this, either with or without VBA? I tried to display below what I would like my table to look like. Assume that for a certain task (service1 in below example) , 50 hours are budgeted. In month 1: 10 hour spent. In Month 2: 5 hours spent. In month 3: 20 hours spent. I would like my pivot table to display the actual hours spent per month, followed by a column which displays the total of the actual hours spent, followed by a column displaying the budgeted hours for this task (and then another column to discplay the difference between both - I kno whow to do this part though). I hope the image below clarifies this description.

TASK Month 1 Month 2 Month 3 Total months Budgeted Difference
service1 10 5 20 35 50 15

So basically my question is: can I have 2 value fields in one pivot table, where one of the value fields is distributed over different columns and also displayed as total, whereas the other value field only displays the total (not distributed over diffrerent columns)?

Any suggestion would be most welcome!

Thanks a lot,
sven
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can't do that using the pivot table's built-in totals. What doe your source data look like.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It depends. If your data source is OLAP, or if you have PowerPivot so you can make an OLAP source out of your Excel data, then you can create sets and only show the columns you are interested in.
 

SvenBlomme

New Member
Joined
Nov 28, 2011
Messages
13
You can't do that using the pivot table's built-in totals. What doe your source data look like.

Hi Andrew, thanks for replying. My source basically consists of one sheet with several columns, where one column displays the departmane name, another one for the service description, one for the date of the effort (which I use to determine the month and year), and another one containing the actual time spent. The budgeted time is located in another sheet, so I merge both sheets using VBA code before I create the pivot table (this piece of code has been tested and works very well).

Sheet 1:
department service date of service time spent
DEP1 Service1 dd/mmm/yyyy 0.5
DEP1 Service1 dd/mmm/yyyy 2.5
DEP1 Service2 dd/mmm/yyyy 1.5
DEP2 Service3 dd/mmm/yyyy 1.25

Sheet 2:
department Service time budgeted
DEP1 Service1 20
DEP1 Service2 40
DEP2 Service3 15


I thought about assigning a fake date to the second sheet (e.g. using the year 2099 or similar), so it will show up on the far right of the table, but this does not give me the lay-out I am looking for...
 

SvenBlomme

New Member
Joined
Nov 28, 2011
Messages
13

ADVERTISEMENT

It depends. If your data source is OLAP, or if you have PowerPivot so you can make an OLAP source out of your Excel data, then you can create sets and only show the columns you are interested in.

I must admit I had to look up what OLAP means. Don't think my data source is OLAP. Also I don't have PowerPivot unfortunately...
 

SvenBlomme

New Member
Joined
Nov 28, 2011
Messages
13

ADVERTISEMENT

My merged data looks very similar to my source. the VBA code lists all column headers which exist in the 2 source sheets in a third sheet (on the first row). Then it copies the information from the first sheet in the third sheet, under the correct header. Then it pastes the info from the second sheet into the third sheet, starting at the first empty row, also under the correct header. So basically I have some columns in which every cell is populated, and some columns where only part of the cells are populated.
 

SvenBlomme

New Member
Joined
Nov 28, 2011
Messages
13
The idea was that I would leave the date for the budgeted hours blank, to avoid that it is displayed per year and per month when I add these 2 as column fields, since I only want to see the total number of budgeted hours...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,336
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top