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

# Color When Greater Than % Target

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

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

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

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

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

Sorry to jump in Joe.....

I should have stopped.

Thanks,

Shin

• ###### 6. Re: Color When Greater Than % Target

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