1

I have a table, that keeps track of players scores for a board game. The relevant columns in the source table are Player Name, which is just text, Game Number, which is unique for each game played, and 'Won?', which is 1 if the player won that game, and 0 otherwise. TThere are other columns, but they are just used for grouping.

|| Player  |  Game No. | Won? ||
--------------------------------
|| John    |     1     |   1  ||
|| George  |     1     |   0  ||
|| John    |     2     |   0  ||
|| Paul    |     2     |   1  ||
|| Ringo   |     2     |   0  ||
--------------------------------

Relevant value fields in pivot table:

  • 'Win Count', calculated as Sum of Won?

  • 'Games Played', calculated as Count of Game No.

I want to add a 'Win Rate' column, which would basically be 'Win Count'/'Games Played' or 'Win Count'.

I tried to add a calculated field 'Win Rate' as "=COUNT(Won?)/COUNT('Game No.')", but this made everything 1, regardless of the actual value.

0

The percentage of True items in a list is the average of zeros and ones, where True is represented by 1 and False by 0.

Just drag the Won field into the values area, and change the aggregation from Sum to Average:

enter image description here

Got this from my good pal Doug Glancy's blog at http://yoursumbuddy.com/percent-of-true-items-in-a-pivot-table-field/

  • This is what I ended up doing. Slight followup: Is there any way for my Won? column to display Yes or No for 1 and 0 respectively? I was thinking there might about doing it this way, but it would make aggregation more confusing. – Will Schnicke Apr 4 '17 at 17:58
  • Yes, but it's convoluted and requires VBA. See my answer at stackoverflow.com/questions/32767117/… – jeffreyweir Apr 4 '17 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.