
1. Re: How To Calculate the Average Test Score by Tercile
Jim Dehner Apr 11, 2018 6:47 AM (in response to Edd Friedman)1 of 1 people found this helpfulHi Edd
This problem requires several steps and without your workbook it would be difficult to explain
Last week I did something similar for some one who wanted to look at sales in quintile buckets  by State (US), category and year
I attached a T10.2 workbook for that solution  the general approach it to determine the boundary values between groups using the Percentile function
Then assigning records into groups based on those boundaries and then using nested LOD's expressions that support varying the groups and year while maintaining buckets at the state level
For presentation purposes I used a map of the US with years on pages to provide a dynamic view of how the sales quints changed with time
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

2. Re: How To Calculate the Average Test Score by Tercile
Edd Friedman Apr 11, 2018 7:26 AM (in response to Jim Dehner)Jim,
THANK YOU! I think this is what I need. I took a quick look at your workbook and realized that part of my problem was that I was trying to use RANK_PERCENTILE() instead of PERCENTILE() which resulted in an error about mixing aggregate and table calculations.
I will investigate further tonight and let you know how it works out.
THANKS AGAIN!

3. Re: How To Calculate the Average Test Score by Tercile
Jim Dehner Apr 11, 2018 7:28 AM (in response to Edd Friedman)Thanks
Percentiles are one of the most misunderstood functions in Tableau (I might write a paper on that)
I used the model I sent you in a presentation last night 
after going through it I think they understood how to put it together
If you have any questions let me know
Jim

4. Re: How To Calculate the Average Test Score by Tercile
Edd Friedman Apr 12, 2018 12:15 AM (in response to Jim Dehner)Jim,
I have attached a version of my workbook. I could not figure out how to apply LOD to the calculation of the Tercile values. However, I believe that I was successful in calculating them correctly without LOD expressions.
Now I cannot figure out how to calculate the average of the scores within each tercile. Any help is greatly appreciated.
Edd

TestAnalysis.twbx 2.2 MB


5. Re: How To Calculate the Average Test Score by Tercile
Jim Dehner Apr 12, 2018 6:29 AM (in response to Edd Friedman)Hi Edd
see the attached
There are a lot of moving parts in your book and I am not versed in what you are actually goals 
I understand that you want to count the number of something in terciles but not clear what that is
 number of students by year/test grade /test window based on AVG score
 number of students by year/test grade /test window based on min score
 number of scores by year/test grade /test window
 a straight record count in each group
 or maybe something else
In your analysis you will want to determine the boundary point between the terciles based on that combination of dimension AND fix that number for comparing the actual measure to count how many fit in each group
That is where the LOD's come in 
there are 2 sheet (red tabes) in the workbook the first sheet looks like this
the first is the T1 calculation from your book  not an LOD
The second uses an lod to include the dimensions on your columns shelf
and the third nest the LOD form 2 to fix the result
In this view they all return the same values leaving the impression that you could use any of them to count the members in each group
But on the second tab I added Person ID  assuming that you wanted to put people into the groups and then do a count
both the first and second calculation above will vary by person  so are not a boundary that can be used to group people 
only the third  nested LOD is a fixed boundary that can be used for grouping
OK so what  the next step is to group something into the 3 pieces created by the boundaries but
I don't know enough about the goals her to first assure we are looking at the right level and second know what to place in each group and count
Does that make any sense?
JIm

TestAnalysis_v10.2 jd.twbx 2.3 MB