I have data, by Department by employee, for various tasks.
Dept Emp Task DueDate IsOverdue
----------------------------------------
A Joe Report 12/12/2018 0
A Sam Report 12/2/2018 1
A Sam Review 12/2/2018 1
B Moe Report 12/12/2018 0
B Pam Review 12/2/2018 1
C Tim Report 12/12/2018 0
C Tom Review 12/2/2018 1
C Jim Report 12/1/2018 1
C Dom Review 12/2/2018 1
C Sal Review 12/22/2018 0
I want to have a pivot table like
Dept Task Percent Overdue
A Report 50
A Review 100
B Report 0
B Review 100
C Report 50
C Review 67
I don’t know how to do “Percent Overdue”. The “Value Field Settings” / “Show Values As” have various Percentage options but none of those return the correct percentages.
If I read you correctly the problem is that you want % of a pair of categories i.e. Dept & Task while the pivot works on a single parameter e.g. parent, row, column
What I think you want is a formula sum(IsOverDue)/count(isOverDue) which doesn’t work in a pivot table
I’d add a column to your data table “Due” with the value 1 (allowing you to have 0 if the report is not actually required).
Then you get the result you seek with the formula sum(IsOverDue)/sum(Due) and you can manipulate the pivot as you want.
(apologies that sounds incoherent geekspeak, but it’s early here and I’m under caffinated.)
Setup your pivot table as a power pivot, then you can add a new measure with sum(IsOverDue)/sum(Due).
In case you don’t have Excel setup for a power pivot, click on File and select Options then Add-ins, you should have an option to add power pivot to your excel if your using 2016.
Oh, I guess I should add for those who are not familiar with power pivot, when you are in your data table you need to click on the power pivot tab and then select “Add to Data Model”. From there a new screen will open up where you can add the power pivot or manipulate your table further before adding a pivot. Of course you could always add a new column in your data table that sums up over due and divides by the total due by employee/department.