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

    Different Numerical Thresholds for Measure Values?

    Sara Lemme

      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?
          Bill Lyons

          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?
            Sara Lemme

            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 IDTest A Total ScoreTest B Total ScoreTest C Total Score
            00001251154
            00002301004
            00003161254
            0000430803
            0000528902
            00006321505
            00007241354
            0000814853
            000090903
            0001071151
            1 of 1 people found this helpful
            • 3. Re: Different Numerical Thresholds for Measure Values?
              Bill Lyons

              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?
                Sara Lemme

                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?
                  Bill Lyons

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