2 Replies Latest reply on May 3, 2017 12:37 PM by E Keeley-Arnold

    Custom reference lines based on dual axis

    E Keeley-Arnold

      Hi Folks,

       

      I've been reading about reference lines and parameters in the whitesheets and forums to find an answer (also tutorial videos), but no luck so far. The aggregate options are not what I need. Maybe reference line isn't the right term, but I don't know what else to call it.

       

      I have a scatter chart showing rates( y ) over median household income( x ). I want to display three reference lines to easily identify rates that are above/below 1%, 2% and 5% of the monthly MHI. I want the lines color coded (preferred) or at least labeled. My reference lines would use the formula: y = 0.02(MHI)/12  (repeat with .01 and .05). The MHI is for the year, hence dividing by 12.

       

      I've tried a couple strategies

      1) I added another column of data that calculates this based on the data I have. This gives me four charts, one scatter of the rates and one for each line. I want the lines and scatter on one chart.

       

      2) I created a calculated field based on the annual MHI field (MHI Est. 2014 ****), and then a parameter based on that. I don't know what to do with it from there.

       

       

      Ideally, I'd like it if I could just calculate my own ref. line based on the axis values, rather than existing data values, but that doesn't seem to be an option yet.

       

      Any help is much appreciated!

        • 1. Re: Custom reference lines based on dual axis
          Justin Larson

          lay out your marks are shown here: Using Measure Values allow you to share the second axis and use lines as a mark type separately from the points.

          1 of 1 people found this helpful
          • 2. Re: Custom reference lines based on dual axis
            E Keeley-Arnold

            Thank you! This is great!

             

            For the benefit of others who may try to do this, below is a description of how I made the changes. I applied this to my more-complex version, and had a bit of trouble at first.

             

            As I looked at your example I found #Measure Values under the list of Measures in the left pane, so dragged that over to marks and removed what I didn't need.

            Then it was still showing as separate graphs, but I saw that the row pills in your example each had straight edge where they meet, so I right-clicked on the Measure Values pill and clicked the Dual Axis option.

             

            Now I have all three lines and the scatter on one chart! The next issue was the % lines appeared inaccurate, but then I noticed the right and left axes had very different ranges. I right clicked on the left axis and clicked Synchronize Axis. Since the 5% line was way above all my values, it made the scatter too small and didn't seem relevant anymore, so I removed that line.

             

            I also noticed that the tooltips are different for the Measure values versus Rate 1000cf. I don't mind, because it is displaying exactly as I would like. I didn't know we could have different tooltip templates for different measures. I like that feature.  One other thing, in the left pane under Marks it says Sum(Rate 1000cf), which I find odd because it's just the values, not aggregate/sum.  Again, I don't mind because the graph is as it should be.

             

            I forgot to mention in the last post that I am using Desktop Version 10.2.1

             

            Thanks again for the help!