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.
1 of 1 people found this helpful
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.