Add calculated field to Pivot Table where one of the formula inputs is the count of all records?

1

I can't figure out what formula I should use to create the following pivot table:

I have a table that contains tasks, assignees, and % complete for each task. I want to create a pivot table that will have columns for assignee, count of tasks, and the total percentage complete for all the assignee's tasks

For example, if one assignee has three tasks with % complete of 90%, 80%, and 15%, respectively, I'd like the third column of my pivot table to reflect a total % complete of 61.7% ((90+80+15)/3 = 61.7).

I tried =SUM('% passed')/COUNT('assignee'), but I get a garbage value. I think what the pivot table is doing is taking a "sum" of the individual row, a "count" of the individual row, dividing the two, then summing all values into my pivot table column. This is obviously not what I want; I want to use the total summation of % passed for all records, the total count of all records, divide the two values, then put that value into the pivot table.

I'd like to do this without just tacking a "dumb" column next to my pivot table data.

the_meter413

Posted 2019-10-31T19:11:48.180

Reputation: 157

No answers