Dynamic target line in Pivot Chart?

Dear Excel Fans,

I would really need some advice on how to create dynamic target line in Pivot Graphs. I did a lot of research, also found here a few topics where it’s mentioned, but I couldn’t find anything similar to the report I need to do.

Therefore I spent some time (like a lot :D) to put together an example that represents what I will need. (Apologies for the very detailed example. It might look weird and maybe even some parts not clear why created they way they are, but it very closely mirrors the actual report I’m working on).

Here we go:

The given company has 6 Sales Agents, selling a certain product to Small, Medium and Large businesses in two locations in the United States, New York and Los Angeles.

• Agent1: belongs to SBD department that deals with Small businesses in New York
• Agent2: belongs to SBD department that deals with Small businesses in Los Angeles.
• Agent3: belongs to MBD department that deals with Medium businesses in New York
• Agent4: belongs to MBD department that deals with Medium businesses in Los Angeles
• Agent5: belongs to LBD department that deals with Large businesses in both New York and Los Angeles
• Agent6: belongs to LBD department that deals with Large businesses in both New York and Los Angeles

Each Sales Agent, Department and Location has weekly / monthly / yearly targets:

• Nr. Of Closed Deals: represent the target of closed deals out of all the meetings the Sales Agent has
• Revenue from Closed Deals: represent the target revenue of closed deals out of all the meetings the Sales Agent has

You’ll find the below sheets in the sample excel:

• DATA: that shows all the data pulled from the CRM system
• MASTER DATA: pulled from the DATA sheet with additional columns that helped me creating the pivot tables and charts
• Targets: that shows the targets for each Sales Agent, Department and Location by weekly / monthly / yearly
• PIVOT_Meetings: created to show the results (TTL. Nr. Of Meetings and the number of Closed, Pending and Not Closed deals). I added slicers so I can choose to see the numbers only for certain Location, Department or Sales Agent. There is a small +/- button on the bottom right corner of the pivot chart, where I can also choose to see numbers by years, months or weeks.
• PIVOT_Revenue: created to show the revenue generated (Rev_All, Rev_NY and Rev_LA). I added slicers so I can choose to see the revenue generated only for certain Location, Department or Sales Agent. There is a small +/- button on the bottom right corner of the pivot chart, where I can also choose to see numbers by years, months or weeks.

And now what I like to add:
A DYNAMIC TARGET LINE

In more details:
Please see the attached PDF for examples I created for the PIVOT_Meetings sheet (the requirement s the same for the PIVOT_Revenue sheet).
Basically, what I like to have, is a dynamic target line that is connected to the data on the Target sheet, and moves depending on what I select on the slicer of the pivot chart.
The targets might need to be changed from time-to-time, so would need a solution that changes automatically when the targets change.
If there is even a solution...

I'm unable to upload the sample excel as it's MASSIVE (...). But I uploaded it on SendSpace and available to download from here (if that is even allowed... I'm not spreading any viruses, my file is just too big to upload here):
https://www.sendspace.com/file/o2rgfh

Anyone out there who created anything like this before? In a dream scenario it would be great to have a simple solution, without any VB, but I know that would be asking for too much

Thank you for your feedback in advance!

Originally Posted by dflak

Trim out excess data - we only need enough to try out several cases. Save the file as XLSB - this format takes up a lot less space. If that doesn't get it down to size, try compressing it and attaching it as a ZIP file. The actual compression may not help, but the size limits on ZIP files on the site is larger.

Thanks a lot for the feedback and advice dflak!
I removed a lot of data, still 3.2MB - but I zipped it up and now attached.

I could only update on my Mac as I already left from work - and for some reason the Pivot Charts don't update, not sure why.. Let me know if it shows the updated data for you?

Thanks a million for checking this excel riddle

Originally Posted by dflak

Got it - working it - but it may be a week before I can get back with you.

Thanks a lot dflak and please, take your time! Any help is much appreciated

Originally Posted by dflak

How do you tell the program whether you are looking for Target by Sales Agent, Target by Department or Target by Location?

Also, how do you tell it whether you want weekly, monthly or yearly metric?

These are very good questions, ones I'm also looking answer for

I just know the result I would like to achieve but I have no idea how it can be achieved...

Nothing is written in stone and I'm open for all suggestions / recommendations offered

Originally Posted by dflak

OK, I'll give you some dropdown lists to manage these things.

I can't add anything to a chart that is built on a pivot table. So what I will have to do is build a second pivot table on a hidden sheet (it will be controlled by the same set of slicers) and overlay that chart with named dynamic ranges and build the chart off that. It sounds complicated but isn't - as long as I do things "by the numbers."

You're in luck - they cancelled a big meeting scheduled for today

Hi dflak,
Sorry for the late answer - I was off the grid for the past day, with literally no reception
Thanks a lot for taking the time! Let me know if you have any other questions?

Your help is always massively appreciated

Originally Posted by dflak

Just to let you know, I am still working this. I got into it a bit and then found out that I needed some supporting pivot tables when you select multiple agents, etc. That was last week and my memory has been overwritten. I'm about half way there, but will have to go back and relearn what I did.

Thank you for the update dflak! And thanks a lot lot LOT for helping in this

Originally Posted by dflak

I am partially there.

I have it sort of working for Meetings. Here are the caveats.

I do not accommodate expand and collapse yet......

Thanks a lot dflak! I will only b able to have a detailed look on Monday, but I will do so then and send you feedback! Thanks again

Originally Posted by dflak

I think I finally got the logic on this one. I have it set up for Meetings. Try it out and let me know if this is what you want. If so, I will extend it for revenue and "clean" it up.

When you select "For Period" the program will collapse / expand the pivot table accordingly and the correct data gets put into the table that runs the chart.

Hi dflak,

Thank you s much for taking the time and working on this!
So if I understand correctly from your previous comments, currently it's only showing if Monthly is selected with the "For period". Correct?

- The monthly target by Agent shows when I choose one or more Agents from the slicer. The different targets are also adding up to show the combined monthly target of the 2 or more Agents
- The monthly target by Department shows also when I choose one or more Departments from the slicer. The different targets are also adding up to show the combined monthly target of the 2 selected Departments
- The monthly target by Location also shows when I choose one from the slicer. The different targets are also adding up to show the combined monthly target of the 2 or more Agents

What I also noticed is, the target line moves beautifully, irrelevant of what is selected at "Target by".

So my questions would be:
1. is the "Target by" necessary? Or will it have an importance later?
2. the final report (where I will translate your target line solution) is updated on a weekly basis with new sales results. Will it be somewhat easy for me to update the file on a weekly basis? (I mean if I will need to spend each week 2-3 hours to update the file, then it will be very challenging)
3. if the solution for the same for Revenue as well, then I should be able to recreate it - unless you want to do it to make sure it works properly
4. you mentioned that you will amend it for weekly and yearly view as well. How will I be able to select the period (week / month or year)? Can I stick to the small +/- sign in my original file or will I need a new method?

These are the only questions that I have in mind for now, and you need to know that you are awesome I think this solution will be perfect for me if my questions 2. and 4. can be solved easily

Thanks a lot for all the help!