
1. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 5:56 AM (in response to Zain Abbas)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

Average.twbx 2.3 MB


3. Re: How to calculate an average/What to write in the calculated field to work out an average
Zain Abbas Jun 22, 2018 6:14 AM (in response to Mavis Liu)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/learningteachinginnovation/main/>  LTI Student Statistics<http://intranet6.open.ac.uk/mgtinfo/ietstats/>

image004.png 210 bytes

image003.png 5.6 KB


4. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 6:23 AM (in response to Zain Abbas)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
Zain Abbas Jun 22, 2018 6:31 AM (in response to Mavis Liu)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/learningteachinginnovation/main/>  LTI Student Statistics<http://intranet6.open.ac.uk/mgtinfo/ietstats/>

Sample PRES Tableau.xlsx 12.5 KB

image004.png 210 bytes

image003.png 5.6 KB


6. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 6:46 AM (in response to Zain Abbas)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.
Please find attached workbook.
Thanks,
Mavis

Average.twbx 49.9 KB


7. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 6:59 AM (in response to Zain Abbas)Hi Zain,
So I've made some updates and I've done this:
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

Average v2.twbx 93.3 KB


8. Re: How to calculate an average/What to write in the calculated field to work out an average
Zain Abbas Jun 22, 2018 7:38 AM (in response to Mavis Liu)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/2782278479042/20180622_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/learningteachinginnovation/main/>  LTI Student Statistics<http://intranet6.open.ac.uk/mgtinfo/ietstats/>

image004.png 210 bytes

image003.png 5.6 KB


9. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 7:47 AM (in response to Zain Abbas)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
Zain Abbas Jun 22, 2018 7:52 AM (in response to Mavis Liu)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/learningteachinginnovation/main/>  LTI Student Statistics<http://intranet6.open.ac.uk/mgtinfo/ietstats/>

Sample 2.xlsx 16.1 KB

image004.png 210 bytes

image003.png 5.6 KB


11. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 8:03 AM (in response to Zain Abbas)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

Average v3.twbx 107.5 KB


12. Re: How to calculate an average/What to write in the calculated field to work out an average
Zain Abbas Jun 22, 2018 8:06 AM (in response to Mavis Liu)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/learningteachinginnovation/main/>  LTI Student Statistics<http://intranet6.open.ac.uk/mgtinfo/ietstats/>

image004.png 210 bytes

image003.png 5.6 KB


13. Re: How to calculate an average/What to write in the calculated field to work out an average
Mavis Liu Jun 22, 2018 8:17 AM (in response to Zain Abbas)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
Mavis Liu Jun 22, 2018 8:18 AM (in response to Zain Abbas)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