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)
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
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.
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.
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
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!
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?
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.