10 Replies Latest reply on Dec 29, 2013 2:12 PM by paul yeo

# When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi All

I also read some where this software can show which city contribute low profit margin. It will display red on the map.

My question is can bar chart sales  by brand . The color of the bar it self can turn red when The above condition happened. ?

Hope some one can give me a link.

• ###### 1. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hello Paul,

I did a simple workbook to demonstrate that it is possible a solution for your scenario.

I used the sample Superstore data source that comes with tableau Desktop. I created a bar chart of Sum of Profit per category of product.

I drag Category to Column shelf and Profit to Row shelf

I created a parameter to define the level of profit or threshold for profit. The parameter is used to give the flexibility to change the profit threshold by the end user

I created a calculated field to color code bars signing red to bars below profit level and blue to bars with values greater than profit level. This calculated field is placed in color Mark

Finally I add a reference line to the y-axis to represent the profit threshold.

Check and explore the attached the workbook to learn how to do it. Use the parameter control located at the top-right of the worksheet to see how the bar color change based on the value of the profit threshold

I hope this helps

best

Ramon

1 of 1 people found this helpful
• ###### 2. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Ramon

Thank you for yr example.

i hv check yr file it work.

but it is possible to make it in % < 15% ?

if can that will be great.

Paul

• ###### 3. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Paul,

Yes of course, I use the metrics sum of profit (absolute values) as an example to demonstrate the concept and process for a solution to your requirement.

I edited the workbook adding a new worksheet using profit as percentage computing it using Table (Across). I add other parameter to reflect the profit margin in % and a new calculated field for the color of the bar. Take a look at the worksheet Sheet 2

If you upload your workbook I'll be able to create it for you.

Best,

Ramon

• ###### 4. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Ramon

Yes this is what i want.

Is See your expression for change the color of the bar :-

IIF(SUM([Profit]) / TOTAL(SUM([Profit]))<[Level for profit (%)], 'red', 'blue')

May i know [Level for profit (%)] how to change from 10% to 15% ?

Paul

• ###### 5. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Paul,

In worksheet Sheet 2 use the Parameter Control (slider) at the right-top corner of the view. Use that control to change the % of the profit marging.

Ramon

• ###### 6. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Romon

What i need is expression on GP% cal to be below :-

IIF(SUM([Profit]) / TOTAL(SUM([Sales]))<[Level for profit (%)], 'red', 'blue')

Notice the above i change the Profit to Sales.

And for the chart i need it Rows = SUM(Sales) , and not SUM(Profit).

How to achieve this ?

My purpose is to show those GP% less then X% in Red , when look at sales By Segment.

Paul

• ###### 7. Re: Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Paul,

See worksheet Sheet 4 from the attached workbook with the implementation to your solution.

Notice that:

1.- Y-axis presents SUM(Sales). To do this you have to drag the measure Sales to Rows

2.- On top of bar is the value of GP% computed by cell or Category of product. Note that GP% is a calculated field using the formula you provided, compute using Cell

3.- color code is base on threshold defined by the parameter Level of profit (%), that could be changed by end user using the parameter control (slider) located at the top-right of the worksheet.

I hope this helps,

Best,

Ramon

• ###### 8. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Ramon

Very good of you able to provide what i need.

Can you pls let me know how you add the GP% table on sheet 4 ? As when i remove the sum(sales) , i notice that GP% table still appear there. So you are create a table link with the sales Bar chart. ( very smart ).

Paul

• ###### 9. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Paul,

GP% is added to Label in the Marks, if you need to remove it, just drag it out of Marks.

See the black rectangle indicating it on Marks

I hope this helps

Best,

Ramon

• ###### 10. Re: When GP % LESS THEN 15% OR sales CY VS PY drop >  15 % bar change color to red

Hi Sir