How to determine average per event by year

Question

What I have? I am having a very large table of data containing cricket information.

What I want? I am trying to calculate the average number of runs per match for Australia (and other countries) in years 2013, 2014, and 2015.

What I got? I was able to calculate the average runs per year into a graph and currently I have a bar chart that somewhat looks like this:

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

But I want the 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? Thanks in advance.

Answer 1

Hi Roshan,

Create a new calculated field (use the Analysis menu item then "Create Calculated Field" or right click in the Measures or Dimensions area of the Data Window). The code should be something like:SUM(Runs)/countd(MID) (Field names are case sensitive and should be wrapped in brackets if they contain spaces -- e.g. [Revenue Amount])

Finally you can Group it by country and year by adding them a different dimensions(columns)

I suggest you explore different charts rather than bar graph as you have multiple dimensions you want to group by.