CODEWITHSUNDEEP
tableau-api
Nick Bohl
Nick Bohl

Reputation: 125

calculation to determine average per event by year

I have a very large table of data containing cricket information. At the moment I am trying to gather the average number of runs per match for Australia (and other countries) in years 2013, 2014, and 2015. I was able to get the average runs per year into a graph and currently I have a bar chart that looks like this:

year       2013 | 2014 | 2015
total runs 1037 | 1835 | 177

but I would like one that divides that total by the number of matches per year (6, 13, and 1 respectively) and looks like this:

year                 2013 | 2014 | 2015
avg runs per match   173  | 141  | 177

but I don't know how to conduct a calculation on these numbers to divide that total over the number of games played. There is a column in my set called 'MID' for Match ID. Obviously, summing the number of MID for 2013 would give me the needed number, 6.

Ideally, I would divide the total number of runs by the number of unique items in the MID column, but I do not know how to do this. If this makes any sense at all, would anyone have a simple way of doing this? I would really appreciate it, as I'm essentially experimenting with this on my own and falling way behind on my other projects.

Upvotes: 0

Views: 106

Answers (1)

S. User18
S. User18

Reputation: 712

Assuming you have a column named "Runs" and a column named "MID", then a calculation for Runs per Match would be as follows:

SUM(Runs) / COUNTD(MID)

This gives total runs divided by distinct count of Match ID.

Upvotes: 1

Related Questions