2 Replies Latest reply on May 24, 2016 8:34 PM by Stuart Dunlap

# Dynamically set center of color legend

Hi,

I would like the 'Center' setting of my color legend to dynamically change to the aggregate average of my view.  I can see where I can manually change the center point of the color setting, however is there a way to have the center point dynamically change based on some formula?  Or, is there some other workaround for this.

The two screenshots below help to further explain what I want to do.  The first shows the existing dialog that enables a manual override to the center setting of the color legend.  The next screenshot shows how the midpoint of the color legend defaults to zero - and I would like it to default to 12%, the aggregate average profit ratio.

Any ideas are appreciated!

Stuart

• ###### 1. Re: Dynamically set center of color legend

Stuart,

instead of using Profit Ratio on the color shelf, you can replace it with a calc that will shift every values by the ratio Avg.

calc to put on color shelf:

[Profit Ratio] - WINDOW_AVG( [Profit Ratio] )

this way Profit ratios with values above the Avg will be positive and get green colors and  ratios below the Avg will get a negative value and a red color.

Michel

• ###### 2. Re: Dynamically set center of color legend

Michel,

Thank you for your reply - you got me headed down the right path.  I actually wanted the center point of the color scale to fall on the average profit for all subcategories.  Your solution appeared to provide the average of the profit ratios and I was interested in the scaling to either side of the weighted average profit.

For completeness, I've attached a spreadsheet with the solution.  The worksheet called 'Dyn Color Chg Prof-Window Avg' has your solution.  The worksheet called 'Dynamic Color Change' has the weighted average profit dividing the color scale.

Your reply got me on the right path - thanks again.

Stuart

1 of 1 people found this helpful