7 Replies Latest reply on Nov 18, 2016 9:35 AM by Tom W

    Adding multiple reference lines to a chart?

    Peter Hanges

      Hi all,

       

      Hoping someone can help me figure this out.  In the attached packaged workbook, there's a simple graph which shows revenue by product.  What I'd like to do, is to set individual parameters (I.e. revenue benchmarks) for each product.  Coffee is at 125k revenue, but the target is 150k.  Juice is at 100k, but the target is 125k etc. Is there anyway to do this? I know I can set one reference line target, but it'd be great to be able to set the reference lines by product. 

       

      Thanks for any help!

        • 1. Re: Adding multiple reference lines to a chart?
          Matthew Risley

          Peter,

           

          Are these reference lines being set by just the parameters? or are they being set by something else?

           

          What you can do is do a dual axis chart. Maybe this video can help you with some ideas? How to Display KPIs Next to Bars - YouTube

           

          (don't have Desktop 10 unfortunately)

          • 2. Re: Adding multiple reference lines to a chart?
            Tom W

            The reference lines can read off another measure.

             

            A hacky way to demonstrate this. Create a calculated field called target as;

            IF [Product] = "Coffee" then 150000

            elseif [Product] = "Juice" then 125000

            elseif [Product] = "Milk" then 75000

            END

             

            Drag and drop Target onto the detail button on the marks shelf.

            Edit your reference line and change the 'Value' dropdown to use SUM(Target). You can set an aggregation which suits the data i.e. Minimum or Maximum and add a custom label like 'Target'.

             

            I can't imagine setting targets like this will be very feasible, so it's probably going to make sense to have a 'Target' field precomputed in your dataset. You can drag that measure in the same way as demonstrated above.

            • 3. Re: Adding multiple reference lines to a chart?
              Peter Hanges

              Interesting, thanks for the help guys.

               

              Tom: Let's make this a little bit more complicated, if you don't mind.  Let's say I want to have targets move when a filter is activated.  For example, the global target for coffee is 150k but for Americas, it's 75k.  How would I create moving targets like this? Also, is it possible to have the reference line break and move with each individual bar? I'd like to have an individual line for each product, instead of just one aggregate line. 

               

              Thanks again,

               

              Peter

              • 4. Re: Adding multiple reference lines to a chart?
                Tom W

                You can change the reference line to display per cell to get it to 'break'.

                 

                As I suggested in my previous approach, I think it makes more sense to push your targets into your datasource. Then you would be able to have an Americas, Coffee target of 75k on that record. And the cumulative sum of the coffee targets for all other countries would need to add to 75k so you get a total of 150k when you change the aggregation of the report to look at it globally.

                 

                To demonstrate in the IF scenario;

                 

                IF Product="Coffee" and Country = "US" then 75000

                ELSEIF Product = "Coffee" and Country = "GB" then 50000

                ELSEIF Product = "Coffee" and Country = "CA" then 25000

                .................

                • 5. Re: Adding multiple reference lines to a chart?
                  Peter Hanges

                  Ah, thought I had set it to per cell before! Works great now, thanks. 

                   

                  Great ideas here, Tom.  I think I'll end up pushing the targets to the database like you suggested.  Thanks again!

                  • 6. Re: Adding multiple reference lines to a chart?
                    Peter Hanges

                    Tom, I actually have one more question for you if you don't mind.  What if I'd like the benchmark to be the revenue for the previous year?  How would I write that calculated field? Would it work with filters?

                    • 7. Re: Adding multiple reference lines to a chart?
                      Tom W

                      You should be able to create a calculated field which references last years revenue by using table calculations > Table Calculation Functions

                       

                      I'd take a read into that and prove out the concept just using a regular table, not a graph. Once you've got that figured out, you could incorporate the calculated field into your graph view.