1 2 Previous Next 16 Replies Latest reply on Sep 13, 2013 8:01 AM by Raviteja Katakam

# Colour all values above a constant

I have an aggregate data.

Name           Average

A                    96

B                    45

C                    80

D                    25

E                    51

F                    60

I want to draw a graph between them such that all value above 50 look "Red" and all values below 50 look "Blue"

For A, 1-50 will look blue, the rest will look red

For B, 1-45 will look blue

For C, 1-50 will look blue, the rest red

• ###### 1. Re: Colour all values above a constant

It looks like you want to color part of a bar blue, and the rest red? If not, can you mock up what it would look like?

• ###### 2. Re: Colour all values above a constant

Yes, you are correct.  Forgive me for the bad drawing, but I think the picture will give you a good enough idea.

• ###### 3. Re: Colour all values above a constant

Good Morning Ayush.

Attached is workbook that does what you're looking for. It's a dual-axis bar chart that uses this formula to produce the blue bars:

IF SUM([Sales])<400000 THEN SUM([Sales]) ELSE 400000 END

It looks like this:

Make sure you sync the axis or it won't work correctly. Also you can bring the blue to the front by re-arranging the color legend (if necessary).

--Shawn

1 of 1 people found this helpful
• ###### 4. Re: Colour all values above a constant

Dual axis chart! Of course. Why didn't I think of it before. Thanks a lot, Shawn. Just out of curiosity, in case we wanted to have 3 colours, then what to do?

• ###### 5. Re: Colour all values above a constant

You can use the Measure Values:

It gets a little tricky/busy but doable. (See Attached.)

--Shawn

• ###### 6. Re: Colour all values above a constant

What I meant was for sales itself. Say I want to divide my line into 3 segments. 0-100K Green, 100K-200K Blue, 200K+ Red. Then, what to do?

• ###### 7. Re: Colour all values above a constant

It's the same idea. Use Measure Values to include a duplicate of the Sales field. You calc on the dup would look something like this:

IF SUM([Sales])>400000 AND SUM([Sales])<1000000 THEN SUM([Sales]) ELSE 1000000 END

Results in this:

See Attached.

--Shawn

• ###### 8. Re: Colour all values above a constant

Won't exactly give the graph I'm searching for. I want a graph where 0-200K Yellow, 200K-400K Blue, 400K+ Red - for all SUM([Sales]). Something like the attached image. Using measure values has another major problem - It does not give me the original units.

• ###### 9. Re: Colour all values above a constant

Nice job Shawn. For some reason I was stuck in padding the data out to get two marks.

Ayush, you may want to also consider reference bands as well - http://onlinehelp.tableausoftware.com/v7.0/public/online/en-us/reflines_addbands.html

Then you could have as many colors as you like

• ###### 10. Re: Colour all values above a constant

I know about the Reference Band feature and use it often. Just wanted a similar functionality with the bar-colour as well. Thanks for the refresh, though

• ###### 11. Re: Colour all values above a constant

Ayush, you're right using Measure Values to add the third color messes up the totals. Maybe Alex will have an idea of why and how to fix this. I'm off for a bit chasing down gremlins of my own.

--Shawn

• ###### 12. Re: Colour all values above a constant

Using Shawn's method of stacking the bars with separate measures, we can dispense with the double axis and tweak the formulas a little. Note the order in the Measure Values Shelf is important:

• ###### 13. Re: Colour all values above a constant

Yep that's it. Nice work Alex. Thx.

--Shawn

• ###### 14. Re: Colour all values above a constant

Thank you. This, along with reference bands, will make my dashboards look really neat.

1 2 Previous Next