# Excel Pivot Table Q

**URL:** <https://boards.straightdope.com/t/excel-pivot-table-q/825858>\
**Category:** Factual Questions\
**Created:** [December 9, 2018, 7:02pm UTC](https://boards.straightdope.com/t/excel-pivot-table-q/825858 "2018-12-09T19:02:26Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [December 9, 2018, 7:02pm UTC](https://boards.straightdope.com/t/excel-pivot-table-q/825858/1 "2018-12-09T19:02:26Z")

</div>

I have data, by Department by employee, for various tasks.

```auto

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

```auto

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?

---

<div class="post-metadata">

**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\
**Post date:** [December 9, 2018, 9:30pm UTC](https://boards.straightdope.com/t/excel-pivot-table-q/825858/2 "2018-12-09T21:30:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![redhook1](https://avatars.discourse-cdn.com/v4/letter/r/bc8723/32.png) [@redhook1](https://boards.straightdope.com/u/redhook1)\
**Post date:** [December 9, 2018, 10:53pm UTC](https://boards.straightdope.com/t/excel-pivot-table-q/825858/3 "2018-12-09T22:53:05Z")

</div>

> [@penultima\_thule](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![redhook1](https://avatars.discourse-cdn.com/v4/letter/r/bc8723/32.png) [@redhook1](https://boards.straightdope.com/u/redhook1)\
**Post date:** [December 9, 2018, 11:20pm UTC](https://boards.straightdope.com/t/excel-pivot-table-q/825858/4 "2018-12-09T23:20:57Z")

</div>

> [@redhook1](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [December 10, 2018, 12:51am UTC](https://boards.straightdope.com/t/excel-pivot-table-q/825858/5 "2018-12-10T00:51:56Z")

</div>

Great tips, got it working. Thanks.