5 Replies Latest reply on Apr 12, 2018 6:29 AM by Jim Dehner

# How To Calculate the Average Test Score by Tercile

I have a  data source with the following fields:

• schoolYear - an integer representing the year of the last day of school for any given year
• testID - the unique identifier for each test
• studentID - the unique identifier for a each student
• studentScore - the score the student achieved on a given administration of a given test

I would like to calculate the average student score for the upper, middle, and lower terciles (thirds) for each testWindow of each testID, of each schoolYear. I can't seem to wrap my head around how to accomplish this.

It's easy to calculate the overall average, but I can't figure out how to divide the scores into terciles to get the average for each tercile. Any help is greatly appreciated.

• ###### 1. Re: How To Calculate the Average Test Score by Tercile

Hi 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.

1 of 1 people found this helpful
• ###### 2. Re: How To Calculate the Average Test Score by Tercile

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

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

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

• ###### 5. Re: How To Calculate the Average Test Score by Tercile

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

1. number of students by year/test grade /test window based on AVG score
2. number of students by year/test grade /test window based on min score
3. number of scores by year/test grade /test window
4. a straight record count in each group
5. 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