How to determine average per event by year

0 votes

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.

May 8, 2018 in Tableau by Roshan

1 answer to this question.

+1 vote

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.

answered Sep 25, 2018 by Machdata
• 340 points

Related Questions In Tableau

0 votes
1 answer

How to divide one measure by another?

Yes it is possible, Option 1: Create a calculated ...READ MORE

answered Apr 10, 2018 in Tableau by ffdfd
• 5,550 points
0 votes
1 answer

How to dynamically determine and categorize duplicate value in Tableau?

Apparently you define a duplicate records as ...READ MORE

answered Apr 13, 2018 in Tableau by xyz
• 1,560 points
0 votes
1 answer

How to change months for a calendar year

Hey, you can try this & Change ...READ MORE

answered Apr 20, 2018 in Tableau by QueenBee
• 1,820 points
0 votes
1 answer

How to calculate weekly average from daily data?

You can create these calculated fields: Unique Weeks ...READ MORE

answered Jun 27, 2018 in Tableau by Atul
• 10,240 points
0 votes
0 answers

Calculating count average from averages

I've a calculated field in Tableau which ...READ MORE

May 28, 2018 in Tableau by Mahima Choudhary
0 votes
1 answer
0 votes
1 answer

How to Split a Measure in Tableau?

Have you tried using an IF statement: IF ...READ MORE

answered Aug 14, 2018 in Tableau by AwesomeSauce
• 860 points
0 votes
1 answer

Sum of previous two months sales where the current month is entered by the user.

It can be achieved easily by creating ...READ MORE

answered Aug 14, 2018 in Tableau by AwesomeSauce
• 860 points
0 votes
1 answer

How to add grand totals of the bar in Stacked Bar in Tableau

Hi Grim, As you have used reference line ...READ MORE

answered Sep 24, 2018 in Tableau by Machdata
• 340 points
0 votes
2 answers

How can I display filtered value and grand total in the same sheet?

You can create a fixed calculated field ...READ MORE

answered Sep 15, 2018 in Tableau by Machdata
• 340 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP