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.
Best Answer
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:
Got this from my good pal Doug Glancy's blog at http://yoursumbuddy.com/percent-of-true-items-in-a-pivot-table-field/