Get pivot table to show row label if no data is there

Hi & welcome.

I wonder if another approach might be better? Depends on what you are doing... Some sample data would help to better understand your requirement.

Such as, to compare two lots of data, create one pivot table from all the data. If wanting to compare say costs from two sources of data, a good technique is to combine all the data except for one lot of costs multiply the values by -1. The resultant pivot table summing on this cost field will show the variances between the data.

The data can be physically made into one source data table and the -1 applied to one lot of costs or the two tables can remain separate and unchanged with the manipulation done in SQL, like below, if you create the pivot table from the two sources 'in one hit'. This thread has plenty of discussion about making a pivot table from multiple sources http://www.mrexcel.com/forum/showthread.php?t=315768

HTH, Fazza

My data is from one source



The problem is I want to also have a field called Billable_hrs but I want all the billable hours, not just the billable hours for Sick and Vacation as a work_type.

I basically need a sum of Sick and Vacation hours_actual and a Sum of hours_billable for ALL types of work (there shouldn't be any for just sick and vacation)

I don't see the posted image, only a place holder.

Can you post some sample data using an HTML maker from a sticky thread at the top of the screen? Please ensure the sample data covers the situation you are describing so that it is simple to understand.

Thanks

http://i26.tinypic.com/2nlxzfb.jpg

Try that, the post says the html maker is no longer available.

The problem is I want to also have a field called Billable_hrs but I want all the billable hours, not just the billable hours for Sick and Vacation as a work_type.

I basically need a sum of Sick and Vacation hours_actual and a Sum of hours_billable for ALL types of work (there shouldn't be any for just sick and vacation)

Click to expand...

bump... any help?

Access to the linked site isn't available to me - security restrictions at my workplace, I guess. Regardless, it sounds like if you combine all the data into a single pivot table it'll do what is wanted. My earlier link refers.

the problem is all the data already is in one pivottable the problem is that I need a filter on one set of data but not on the other

The situation & requirement are unclear to me. And becoming unclearer.

I thought there were two pivot tables though I now understand the data is in one pivot table, and the difficulty relates to filtering in that pivot table.

Can you explain what filtering is required?

Sorry if I have not been clear enough, I will try to explain it clearer:

I have one large pivot table. Fields I am interested in that pivot table are:
Date_Start
Member_ID (Name of the person)
Billable_Hrs (Hours billed by the person)
actual_hours (How many total hours they reported... should equal 40 most of the time)

Now both Billable_Hrs and actual_hours have a field assigned to them called work_type which is the final field I am interested in.

I am looking for total Billable_hrs by Member_ID and Date(put both into row labels)... I have had no problem doing this.

I am also looking for actual_hours, but only actual hours filtered by Vacation, Sick and Training by Member_ID and Date. I have had no trouble doing this.

Now I need both the data in the same row to do calculation based on both but if a Member does not have any sick, vacation or training time they do not show up so I can not consistently know what field to use.

I tried putting both in one pivot table but the problem is the Billable_hrs gets caught in with the work_type filter and it only shows me Vacation, Sick and Training billed hours. Which I want the total of (actually I want the total minus Training billed hours because those are not actually billed out).

I would take the work_type filter off and just have the long list of types of work but the problem I am worried about is that if there is a work_type (column) that has no data it will not show when I refresh the table and if there is data in that section I will miss it because i can either =GETPIVOTDATA for all the fields so I would not get that data and if we add or remove a member the information will not be correct because I have not done the =GETPIVOTDATA commands for that name. I was hoping for this to be a well integrated sheet so that it will work if more or less member_ids get added/removed. On the other hand I could SUM a row but if another column gets added or erased it is all messed up.

Hopefully this details my problem a bit better. Sorry my original post was not this detailed.