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

    Dynamically set center of color legend

    Stuart Dunlap



      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!





        • 1. Re: Dynamically set center of color legend
          Michel Caissie



          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.



          • 2. Re: Dynamically set center of color legend
            Stuart Dunlap



            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.



            1 of 1 people found this helpful