Excel Pivot Table Q

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.

Any ideas?

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.

Great tips, got it working. Thanks.