I have a follow up to a recent post that was answered on this forum:
I'm hoping there is an easier way to do what I'm trying to accomplish - which is a little more involved than the post referenced above. I would like the color range of my marks to dynamically center on the weighted average profit ratio and I would like the color representing that weighted average to be the center of the color range.
The attached workbook helps explain what I want. In the worksheet called 'Dynamic Color Change' I'm using the formula 'Profit Ratio Window Calc Avg' to dynamically center the color on the overall profit ratio for all categories. The issue is, what's displayed as the range of values on the color legend is the formula I had to use to dynamically center the color legend. (This is the solution outlined in the post referenced above. In this case, the unfiltered worksheet view is centered on 12.5%.)
I want to keep the color range as it is, however I would like the range to show as the Min & Max of the sub category profit ratios - in this case I would like the lower end of the color legend to show -8.6% and I would like the upper end of the color legend to show 44.4%. The only workaround I've found is in the 'Profit Dashboard' worksheet of the attached Tableau workbook... Here, I had to format the color legend, removing all reference to the formula values, and then I had to build individual worksheets to show the Min and Max range. (The dashboard is using the worksheets 'Dynamic Color Change Min' and 'Dynamic Color Change Max' as floating worksheets on the dashboard to show them as the values on the color range.)
I hope this all makes sense. Is there a way to do this in Tableau that does not require the manual workaround and creating the extra worksheets?