Good Morning,
I have been searching for a couple of days and cannot seem to find the answer for my question. It seems like most are looking to exclude nulls. I am in the process of meeting with 200 managers regarding their 90 departments, which is 85000 rows of data. What I am showing is their access of their staff to certain applications so the pivots looks like this:
Department App1 App2 App3 App4
Dept1 90 90 89 87
Staff 1 1 1 1 1
Staff 2 1 1 1 0
Staff 3 1 1 0 0
Dept 2 50 45 30 10
Staff 1 1 1 0 1
Staff 2 1 0 0 0
Staff 3 1 0 1 0
So basically App1 is an application that the entire company should have, App2 probably is too but some where missed, App3 to review... etc. When you look at the numbers of Dept1 and 2 its not exactly helpful if we don't know how many are in the full department. So I want to show the percentage of the departments for Dept1 and Dept2 to show 100%, 90%, 10%, etc. but still show 1 or 0 in the pivot. Is there an option for this that I have been missing? I have tried playing around with subtotal formatting and show values as % of parent total, which seems to be close but not quite since it is rolling up to the Dept1 as 100% and breaking the staff into smaller percentages. I almost want the inverse, except that Staff values would remain as 1 or 0 and the subtotals change to an all inclusive average. I appreciate any assistance
I have been searching for a couple of days and cannot seem to find the answer for my question. It seems like most are looking to exclude nulls. I am in the process of meeting with 200 managers regarding their 90 departments, which is 85000 rows of data. What I am showing is their access of their staff to certain applications so the pivots looks like this:
Department App1 App2 App3 App4
Dept1 90 90 89 87
Staff 1 1 1 1 1
Staff 2 1 1 1 0
Staff 3 1 1 0 0
Dept 2 50 45 30 10
Staff 1 1 1 0 1
Staff 2 1 0 0 0
Staff 3 1 0 1 0
So basically App1 is an application that the entire company should have, App2 probably is too but some where missed, App3 to review... etc. When you look at the numbers of Dept1 and 2 its not exactly helpful if we don't know how many are in the full department. So I want to show the percentage of the departments for Dept1 and Dept2 to show 100%, 90%, 10%, etc. but still show 1 or 0 in the pivot. Is there an option for this that I have been missing? I have tried playing around with subtotal formatting and show values as % of parent total, which seems to be close but not quite since it is rolling up to the Dept1 as 100% and breaking the staff into smaller percentages. I almost want the inverse, except that Staff values would remain as 1 or 0 and the subtotals change to an all inclusive average. I appreciate any assistance
