1 2 Previous Next 18 Replies Latest reply on Jun 25, 2018 1:47 AM by Mavis Liu

How to calculate an average/What to write in the calculated field to work out an average

Afternoon,

Essentially I am trying to work out the average % from 2017 and the average % from 2018 so I can then compare the two.

Can anyone help me with writing a syntax/showing me how to calculate the average for this graph. Even though it looks very simple, I'm finding it very difficult to do in Tableau!!

Thanks

• 1. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

Like this? -

I used an LOD calculation:

I used an exclude because I thought you might be using filters in your view.

Then I made this discrete and put it inbetween year and wording.

Thanks,

Mavis

• 2. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi, Zain

If you want to see more visualization way, you can use the reference line as shown below to show the avg as reference line

Hope this helps

ZZ

• 3. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Mavis,

That is what I am looking for however the SUM (Value) is causing me a problem as I am counting the number of students who have selected ‘Agree’ which is a text response therefore it is not picking it up

Regards,

Zain

Zain Abbas | Strategic Analyst

Quality Enhancement & Learning Analytics, LTI

The Open University, 1st Floor Jennie Lee Building, Walton Hall, Milton Keynes, MK7 6AA

Tel: +44 (0) 1908 655750 | LTI Intranet<http://intranet6.open.ac.uk/learning-teaching-innovation/main/> | LTI Student Statistics<http://intranet6.open.ac.uk/mgt-info/iet-stats/>

• 4. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

Sorry I'm not sure what you mean, would you mind attaching a sample of the data or a sample workbook so that we're able to have a look at this?

Thanks,

Mavis

• 5. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Mavis,

Attached is an example of the data I am using.

Regards,

Zain

Zain Abbas | Strategic Analyst

Quality Enhancement & Learning Analytics, LTI

The Open University, 1st Floor Jennie Lee Building, Walton Hall, Milton Keynes, MK7 6AA

Tel: +44 (0) 1908 655750 | LTI Intranet<http://intranet6.open.ac.uk/learning-teaching-innovation/main/> | LTI Student Statistics<http://intranet6.open.ac.uk/mgt-info/iet-stats/>

• 6. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

I've updated your dataset so that some of the years are 2017 just so it looks similar to yours.

Is this what you're looking for?

{EXCLUDE [Wording], [Simplified Text Response] : AVG(

{INCLUDE [Year], [Wording], [Simplified Text Response]: SUM([Numeric Value])})}

So in this example, I am summing up every year, word and simplified text to find the value, then within the year, I am finding the average per year.

So e.g 2017 has 3 rows of data which have values of 19, 18 and 10. The average of these three is 15.67.

Thanks,

Mavis

• 7. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

So each bar represents what % of that wording in that year was 'Agree'. So in 2017, 100% of the students agreed with 'As a result of my experience...' etc etc.

Yet in 2017, only 50% of students agreed with 'there is adequate provision...' etc etc.

Now for each year, I've worked out the average based on these responses.

So for 2017, the 3 results were 100,100 and 50, the average of these 3 is 83% . This is the way I've worked it out which is an average of an average.

However I have another field which works out the WEIGHTED average which is :

Both have returned 83% in this case, but it depends how you want your average to be calculated.

Thanks,

Mavis

• 8. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Mavis,

Thank you for having a look at the sample however showing that 100% agree with one of the statements isn’t accurate.

https://community.tableau.com/servlet/JiveServlet/showImage/2-782278-479042/2018-06-22_13h54_32.png this picture you produced earlier is what I would like but of course the wording and SUM(Value) would have to be different.

Is it complicated to do this?

Regards,

Zain

Zain Abbas | Strategic Analyst

Quality Enhancement & Learning Analytics, LTI

The Open University, 1st Floor Jennie Lee Building, Walton Hall, Milton Keynes, MK7 6AA

Tel: +44 (0) 1908 655750 | LTI Intranet<http://intranet6.open.ac.uk/learning-teaching-innovation/main/> | LTI Student Statistics<http://intranet6.open.ac.uk/mgt-info/iet-stats/>

• 9. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

That's because in your dataset you did not have 2017 so I just changed some of the 2018 year numbers to be 2017.

Please provide a dataset which you'd like me to use which has the 2017 figures which reflect your initial question.

Thanks,

Mavis

• 10. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Mavis,

I see – I have attached a sample with 2017 figure in.

Regards,

Zain

Zain Abbas | Strategic Analyst

Quality Enhancement & Learning Analytics, LTI

The Open University, 1st Floor Jennie Lee Building, Walton Hall, Milton Keynes, MK7 6AA

Tel: +44 (0) 1908 655750 | LTI Intranet<http://intranet6.open.ac.uk/learning-teaching-innovation/main/> | LTI Student Statistics<http://intranet6.open.ac.uk/mgt-info/iet-stats/>

• 11. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

Please see the attached workbook. I haven't changed anything in the file apart from replacing the data source such that it's using your sample 2 file:

In this case, 2018, Wording 'As a result of my...' is 100% as I've checked the dataset:

Thanks,

Mavis

• 12. Re: How to calculate an average/What to write in the calculated field to work out an average

Thank you Mavis – one more thing (sorry I’m starting out on Tableau hence the basic questions!)

How did you get the AGG(% of students which agree) pill?

Regards,

Zain

Zain Abbas | Strategic Analyst

Quality Enhancement & Learning Analytics, LTI

The Open University, 1st Floor Jennie Lee Building, Walton Hall, Milton Keynes, MK7 6AA

Tel: +44 (0) 1908 655750 | LTI Intranet<http://intranet6.open.ac.uk/learning-teaching-innovation/main/> | LTI Student Statistics<http://intranet6.open.ac.uk/mgt-info/iet-stats/>

• 13. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

This is a calculated field, you can create this by clicking on the dropdown or right clicking in the data pane:

If you right click on the pill in the data pane and select edit:

You can see the calculation:

The calculation means that whenever the simplified text value is Agree, it will give it a value of 1, then you can sum these all up and divide it by the total number of records.

Thanks,

Mavis

• 14. Re: How to calculate an average/What to write in the calculated field to work out an average

Hi Zain,

If my previous response and attached workbook is correct, please mark my answer as correct so that this thread will have the updated status as 'Answered'. Marking is as correct is only available if you open the thread and is not available if you open this thread in your messages.

Thanks,

Mavis

1 2 Previous Next