+ Reply to Thread
Results 1 to 3 of 3

Adding a Dynamic "Target Threshold line" to a Pivot table and chart

  1. #1
    Registered User
    Join Date
    12-08-2015
    Location
    Ottawa
    MS-Off Ver
    2010
    Posts
    25

    Adding a Dynamic "Target Threshold line" to a Pivot table and chart

    Threshhold problem.xlsxThresholdImage.png
    Hi All,
    I have attached a File that contains sales store data, per store, per store level.
    What I have done is found the average sales per time interval 00:00- 23:50.
    Now I need to add a Dynamic threshold line on the graph so that we can easily when stores are passing the threshold (see picture).

    I have tried using a calculated item which is perfect, BUT then I cant compute the average, Calculated fields create TOO many columns.

    Does anyone have a solution, either with regular Excel tools or even VBA?
    ***This needs to stay as a Pivot table, cannot be a "Regular graph, data must be dynamic"

    Thank you,
    ImokatExcel

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Adding a Dynamic "Target Threshold line" to a Pivot table and chart

    I need additional information. It's fairly obvious that the time range is fixed. Are the levels fixed as well? Will there always be just two of them or can there be more?

    If they are fixed then you could make a "shadow" pivot table in helper columns next to the Pivot table. Basically set Cells in Column F equal to the cells in Colum D and the same with G and E. Then build your chart off these columns and it will be a regular chart.

    If the number of levels is variable, then a VB solution is needed to copy the results of the pivot table into an Excel Table and then base the chart off the Excel table. When you change a season or store filter it will kick off this code again since these filters may affect the number of levels.

    You should be able to do the first method (where number of levels is fixed) on your own. If you need the VB solution, let me know.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Adding a Dynamic "Target Threshold line" to a Pivot table and chart

    I have to admit, I have some selfish motives for responding to these threads: they give me an opportunity to play with parts of Excel I normally do not get to see. In this case it's a matter learning to work with chart parts and with table parts as list objects.

    I added some things to the existing spreadsheet. I added a table on the sheet called table. I copy the spreadsheet data into this table and I build the chart off this table. Also on the data sheet I added a small "table" containing the start and end times and the threshold for the purposes of charting. I also created a named range: TimeRange =OFFSET(Pivot!$C$6,0,0,COUNTA(Pivot!$C:$C)-4,1). I use this to help find the borders of the pivot table data.


    If you move the pivot table or rename the page, it will break the code, you can see the parts of the code that set.

    I also put a change event on the Pivot page, cells D2 and D3 to kick off the VB Code.
    Attached Files Attached Files
    Last edited by dflak; 01-30-2016 at 03:06 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  2. VBA target line using pivot table bar chart
    By Turmoilz in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-28-2013, 05:44 AM
  3. [SOLVED] Problem creating Line Chart from columns "date" and "money"
    By brosef in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 11-05-2012, 09:01 AM
  4. Replies: 0
    Last Post: 09-17-2012, 01:17 PM
  5. How to change "From" & to have a dynamic "Subject" line in Excel Mailing
    By andy_iyeng in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2012, 03:04 AM
  6. "Dynamic" Line Chart
    By andrewc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-19-2008, 06:07 AM
  7. Replies: 4
    Last Post: 02-03-2008, 05:11 PM
  8. [SOLVED] How do I go from "Chart View" to "Pivot Table Form" in a Pivot ta.
    By Jersey Girl in forum Excel General
    Replies: 1
    Last Post: 03-10-2005, 01:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1