pivot table define layout and totals differently per value field?

[SvenBlomme]

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

 

[Andrew Poulsom]

You can't do that using the pivot table's built-in totals. What doe your source data look like.

 

[RoryA]

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]

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]

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...

 

[Andrew Poulsom]

What does you merged data look like?

 

[SvenBlomme]

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.

 

[Andrew Poulsom]

How are you grouping the dates if that column is blank for budget?

 

[SvenBlomme]

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...

 

[Andrew Poulsom]

Sorry, I don't understand. How did you group the actuals into Month 1, Month 2 etc?