5 Replies Latest reply on Mar 23, 2016 12:37 PM by Bill Lyons

Different Numerical Thresholds for Measure Values?

I am working with a data set that has multiple test scores for students, and to meet a proficiency requirement, they need to meet certain score thresholds that are different depending on what test they take.  I would like to be able to color the values in each column either green (meets requirement) or red (does not meet requirement) based on the varying thresholds so someone can quickly look and see if a student has met the requirement using any of the test scores, but am at a loss as how to have different thresholds for each column in the Measure Values.  Any suggestions?

• 1. Re: Different Numerical Thresholds for Measure Values?

This should be relatively straightforward using calculated fields. Can you attach a sample packaged workbook showing the dataset and how you want the results presented?

• 2. Re: Different Numerical Thresholds for Measure Values?

Hi Bill,

I have attached a packaged workbook with sample (made-up) data.  There are ten students with three different test scores - a score for Test A, a score for Test B and a score for Test C.  If Test A has a score of 30 or higher, the student passed.  If Test B has a score of 120 or higher, the student passed.  If Test C has a score of 5 or higher, the student passed.  What I'd like to do is have passing scores in a different color from non-passing scores in a basic data grid.  Here is what I came up with in Excel as an illustration of what I'd like to do in Tableau.  Your assistance and insight is greatly appreciated!

 Student ID Test A Total Score Test B Total Score Test C Total Score 00001 25 115 4 00002 30 100 4 00003 16 125 4 00004 30 80 3 00005 28 90 2 00006 32 150 5 00007 24 135 4 00008 14 85 3 00009 0 90 3 00010 7 115 1
1 of 1 people found this helpful
• 3. Re: Different Numerical Thresholds for Measure Values?

You will find the simplest solution in the "via Pivot" sheet that I added to your example. In it, I copied your data set into Excel, then used the new pivot feature in the Data Source editor of Tableau 9.0 (same as your posted version) to pivot the test scores into a column for test scores and a column for the test name, renaming the default columns to make it easier to read. I also had to change the default data type for the student IDs to text in Excel and string in Tableau in order to retain the leading zeroes.

Then, I pulled [Test] onto columns and [Student ID] onto rows and [Test Score] onto Text. Next I created a calculated field titled [Test Pass or Fail] to return the appropriate pass or fail value based on the score for the particular test, and your specified passing scores:

CASE [Test]

WHEN 'Test A Total Score' THEN

if [Test score] >= 30 THEN 'Pass' else 'Fail' end

WHEN 'Test B Total Score' THEN

if [Test score] >= 120 THEN 'Pass' else 'Fail' end

WHEN 'Test C Total Score' THEN

if [Test score] >= 5 THEN 'Pass' else 'Fail' end

END

I then dropped that on the Color shelf, and tweaked the colors.

If you really can't pivot the data as I did, there is another way. But it is significantly more complicated (probably 10 times more), so I do not recommend it. But I will share it with you if necessary.

I hope that helps.

1 of 1 people found this helpful
• 4. Re: Different Numerical Thresholds for Measure Values?

Thank you, Bill.  Right now, I am working with an Access data source, but I could fairly easily pop that out into either Excel or a text file to be able to pivot in Tableau.  When I have a chance to get back to this part of the project, I will see if I can get this option to work.  Thank you again!

• 5. Re: Different Numerical Thresholds for Measure Values?

You are welcome. Glad I could help.

Access is a little more challenging. The SQL equivalent of Tableau data source "pivot" is, ironically, "unpivot." While you can't do that directly in Access, there is a workaround. See http://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010 for details. While a little more difficult than the Excel solution, it is still probably easier than the alternative (which is Conditional Formatting | Drawing with Numbers).