3 Replies Latest reply on Jun 13, 2018 3:33 PM by swaroop.gantela

    How do I create a reference line on a different scale than is presented on the axis [Workbook Attached]

    Zamir  Dorochenko

      Attached is a simplified but representative example of my issue (FYI I've simulated the profits column to keep from posting real values).

       

      What I'm trying to do is set a reference line based on the underlying values of a measure that I've transformed in order to give more consistency to the resulting scatterplot distribution per a supervisor's requrest (the profits column is the vertical axis, and has positive and negative values).  Taking the log would drop the salesmen with negative profits, so I've gone with SQRT([Profits] - {[Min Profit]}).  So, the vertical axis units are in the transformed profit values measure called Transformed Profits, but I'm trying to set a horizontal reference line or constant line (whichever is ultimately easiest) at $0.00 in terms of profits

       

      I've already divided the salesmen by color in terms of positive or negative profits in the attached workbook, I just want a horizontal line that DYNAMICALLY separates the red from those in the black, so it wouldn't be acceptable to just find the corresponding value on the transformed vertical scale and set it here. 

       

      Ideally, I could also change the values in the vertical axis to reflect actual dollar amounts while retaining the more homogeneous variation in the profit values (i.e., I don't want to have all of the profit figures bunched up at the bottom with a few outliers).

       

      Image 1 is what I have, Image 2 is what I'd like, and the workbook should also be attached.

       

      Thanks for your patience, this is my first post in the community.

       

      Zamir

        • 1. Re: How do I create a reference line on a different scale than is presented on the axis [Workbook Attached]
          swaroop.gantela

          Zamir.

           

          Welcome to the Forum.

           

          I think I generally see where you're going,

          but I didn't quite catch the part about the line being dynamic:

           

          "I just want a horizontal line that DYNAMICALLY separates the red from those in the black,

          so it wouldn't be acceptable to just find the corresponding value on the transformed vertical scale and set it here. "

           

          I was going to suggest using (SQRT ( 0 - {[Min Profits]}), which seemed like it separated the positive from the negative,

          but it sounded like that was just finding the corresponding zero value.

           

          Would be grateful if you would describe how the line should dynamically change based on filter selections.

           

          272842refline.png

          • 2. Re: How do I create a reference line on a different scale than is presented on the axis [Workbook Attached]
            Zamir  Dorochenko

            Hi Swaroop,

             

            Thank you for working on this, and for the welcome.

             

            In thinking back through what I was asking for by 'dynamic,' my thoughts were still about the potential of determining the horizontal line on the basis of the native profit values (i.e., the non-transformed profits).  My concern was that when these data were updated with new quarterly figures, or salesmen were added or removed, just adding a constant value that worked for the current setup was something I wanted to avoid.  Similarly, if the distribution of in the scatterplot changed on the basis of these updates, and I had to rig up a new transformation to profit to improve the appearance of the scatterplot distribution, I was just hoping the proposed solution would remain valid.

             

            Again, and most ideally, I was looking for a way to compute the dividing line on the basis of the non-transformed data (i.e., native profit values) and simply set it = $0.00.  If this were not possible (I certainly couldn't figure out how), then I was looking for a solution that resulted from a calculation, and wasn't merely a constant (i.e., setting the horizontal line to results from the constant value of 1,182, which results from the calculation).  Again, your solution seems to avoid this. 

             

            What I didn't think of is using a calculation on the basis of the transformed profit values to approximate the $0 value as your solution does, and now that I've played around with changing different values, removing and adding different sales people & filtering, your solution worked consistently, so thank you for the help.

             

            Best,

            Zamir

            1 of 1 people found this helpful
            • 3. Re: How do I create a reference line on a different scale than is presented on the axis [Workbook Attached]
              swaroop.gantela

              Zamir,

               

              Glad that it looks to be working.

               

              Thank you for describing the different considerations.

              I think your transformation is a good one,

              the shifting of the minimum profit to be the baseline.

               

              If you were in need of further transformation, you could

              try the log on your shifted version:

              LOG([Profits] - {[Min Profit]})

               

              If you wanted the baseline to be just the minimum

              of those on the screen after filtering, you can add

              those filters to the context, and the zero line should move appropriately.

               

              Please see workbook attached in Forum thread.