6 Replies Latest reply on Feb 22, 2017 3:36 PM by Brisa Halviatti

    Color When Greater Than % Target

    Brisa Halviatti

      Hello community,

       

      I am looking to color each figure based on whether or not it exceeds a target value.

       

      Context: I work for property management of public housing in Seattle. We want to see if/when the percentage of residents of a particular racial group in a given building is 20% or more than the average percentage of that racial group overall. I have a table that has each property on the rows, and each column shows the % of that property that identifies with each race.

       

      I created this calculated field which gives our current demographics across all properties (although writing a field that calculates that would be even cooler so I don't have to re-run and edit it each month to see if these baselines change)

      CASE [Race]

      WHEN "Asian" THEN 16.95 * 1.2

      WHEN "Black" THEN 38.62 * 1.2

      WHEN "Multi-Race" THEN .78 * 1.2

      WHEN "Native American" THEN 1.64 * 1.2

      WHEN "Not Specified" THEN 6.16 * 1.2

      WHEN "Pacific Islander" THEN .59 * 1.2

      WHEN "White" THEN 35.35 * 1.2

      ELSE 0 END

       

      I tried to write a calculated field along the lines of [Number of Records]  >= [Calculated Field Above], assuming as a True/False it would color any instance that met or did not meet this criteria on a cell by cell basis. It didn't do this, it colored it by column. Any advice on how to do this would be greatly appreciated.

        • 1. Re: Color When Greater Than % Target
          Joe Oppelt

          Attached is an example I whipped up using superstore.  It's V8.2, so it will upgrade to whatever you are on.

          1 of 1 people found this helpful
          • 2. Re: Color When Greater Than % Target
            Brisa Halviatti

            Hi Joe!

             

            Unfortunately this doesn't solve my issue because i'm looking to see if a proportion of the population of residents at a given property exceeds the average proportion of that racial group overall; these proportions are percentages not raw numbers like sales dollars or number of people.

             

            I think that's the issue here, my "Threshold" calculated field is giving a number but i really want it to be a percent, and so when i do a quick table calculation that converts the number of residents into a percent, it's not comparing apples to apples.

             

            I attached for you the same work book but set up with a more similiar problem to the one i'm having. i have tableau 10 though so here's how you can set it up:

             

            I dragged order priority to columns and category to rows; in my data set race would be the columns and property the rows. I dragged number of records to the marks card and changed it to percent of total (across)

             

            The grand totals below are the threshold, so i wrote this calculated field:

            case [Order Priority]

            when "Critical" then 19.15

            when "Not Specified" then 19.91

            when "High" then 21.05

            when "Medium" then 19.42

            when "Low" then 20.48

            else 0

            end

             

            I would want to color any instance where the percent of category has a proportion of order priority that exceeds the thresholds outlined above.

             

            • 3. Re: Color When Greater Than % Target
              Joe Oppelt

              You just have to make your own calcs to do the work.

               

              On Sheet 5 (copy of Sheet 4) I made a calc:  [Your calc in its own calc].  You have a quick table calc, but you can transfer it to your own calc by dragging it from your text shelf into a calc editor.

               

              And I simulated Tableau's grand total math in my own calc as well.  Tableau is taking the sum of all [Number of Records] by [Order Priority], and doing the same math that you see was transferred into [Your calc ...].

               

              So I made a calc to gather the sum([Number of records]) (See [Number of records per order priority].)  And I did the same math using that in [Threshold by Order priority].

               

              Now I have my comparison field.  You can see on Sheet 5 that my math comes up the same (number is positioned under the Order Priority label.)

               

              Then, see [Colors for this example].  It's what I did on Sheet 3, just a little more involved because we have more complicated calcs in play.  But the principle is the same.

               

              Sheet 6 is your original Sheet 4, but with my new Colors calc on the colors shelf.  (Note:  The Colors calc is a measure.  When you first put it on, Tableau gives you a diverging color scheme because it's a measure and tableau assumes a continuous variable.  (See Sheet 7 for what that looks like.)  But I right click on the pill on the color shelf and change it to DISCRETE, which is what you see in Sheet 6.)

              1 of 1 people found this helpful
              • 4. Re: Color When Greater Than % Target
                Shinichiro Murakami

                Hi Brisa

                 

                I am not sure I understand you request correctly or not, but here is something.

                 

                 

                 

                [Colors (Percent)]

                if SUM([Number of Records]) / TOTAL(SUM([Number of Records]))> attr([Thresholds (Percents)]/100) then 1 else 0 end

                 

                 

                Change this field to "Discrete" and put as color.

                 

                 

                Thanks,

                Shin

                • 5. Re: Color When Greater Than % Target
                  Shinichiro Murakami

                  Sorry to jump in Joe.....

                  I should have stopped.

                   

                  Thanks,

                  Shin

                  • 6. Re: Color When Greater Than % Target
                    Brisa Halviatti

                    This was the perfect fix! I knew LOD was going to play into this some how, I really appreciate you walking me through each step!

                     

                    Cheers,

                    Brisa