Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

excel 2010 - Creating Calculated Field with SUM and COUNT of Fields Pivot Table

Here's a snapshot of my pivot table.

enter image description here

I want to create a calculated field that displays the percentage from the Responded column (C) over the Ticket # column (B) as in column G.

The Ticket # column is a column of value text strings (e.g. 413456, 464313) and the Responded column is a list of 1's and 0's. In my pivot table I am taking the count of all Ticket # and the sum of the Responded.

I tried to create a calculated field with the following formula

=Responded/COUNT('Ticket #')

but this gave me the same as Sum of Responded. How can I create a calculated field that gives me the % in column G?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

From my experience and what another SO member posted, COUNT doesn't seem to work well in pivot table field calculations. I managed to count the distinct tickets by creating a helper column of 1's and 0's in my data source. (Thanks to this response from Siddarth Rout.)

Once you have 1's associated with each distinct tickets, I simply created the calculated with Responded/Ticket #.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...