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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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...
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,217,674
Messages
6,137,923
Members
450,098
Latest member
Ikmal Sabri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top