1 Reply Latest reply on Aug 22, 2018 2:03 PM by Ken Flerlage

    Overlaying sheets

    james.evans.10

      Hi All,

       

      I've created a line graph that shows an age-based range of results for a particular test. The range is defined as the 20th percentile and the 80th percentile, with the average in between. I'd like to use this graph as a sort of background and give the end user the ability to select a specific ID Number and see where it falls on the range based on the age and sample amount. For example: ID Number 1000 is 59 years old and has a value of 319, so I'd like the user to be able to pick that ID Number and see an X in the range sheet corresponding to their stats.

       

      Is this possible within one sheet? I have also thought about making the line graph an image and just overlaying a sheet on top of it, but that is not ideal because as more data comes in I would like the line graph to update accordingly.

       

      Any insight would be great, I've attached a sample workbook.

       

      Thanks

        • 1. Re: Overlaying sheets
          Ken Flerlage

          One solution would be to create a parameter (let's call it Select ID) and set up that parameter to be populated from the ID Number field.

           

          Then you can create a calculated field, which I'll call Selected ID Sample, with the following definition:

           

          // Measure will only show the sample for the selected ID.

          IF [ID Number]=[Select ID] THEN

              [Sample]

          END

           

          Then add that to to the list of measures and you'll get something like this:

          This will give you just a single dot because this axis is set as a line chart. If you want this to show as an X or some other shape, then instead of adding it to your measures, drag it up next to Measure Values on the rows shelf:

          Then make that a dual axis and be sure to right click on the y-axis and choose "Synchronize Axis".

          At this point, it still looks the same as the original, but since we have two axes now, we can make the second one a different type of chart. So click on the second axis and change it to a Shape mark type. You can then change the shape as desired.

          The attached workbook has both options on two separate sheets.

           

          If this addresses your question, please be sure to mark this as the "correct answer." This will allow us to close the thread and will also make it much easier for people to find answers to similar questions in the future. Thanks!

          1 of 1 people found this helpful