+ Reply to Thread
Results 1 to 12 of 12

Return a value for items selected in a pivot filter

  1. #1
    Registered User
    Join Date
    02-14-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    7

    Return a value for items selected in a pivot filter

    I have a pivot table that shows weekly sales by sales person and their achievement against a target. The report covers multiple weeks and there is a report filter that allows the user to select a specific week or multiple weeks. The filter list is based on week ending dates.

    The issue I have is that the sales data is pulled from a database and as mentioned can return data for multiple weeks. The targets however are only set by sales person for a week. These targets are manually input in a separate worksheet.

    What I need to do is multiply the target values by the number of weeks that the user selects in the report filter. So for example if sales person Joe Bloggs has a weekly target set at £4000 and the user selects two week ending dates in the report filter I want the report to show the two weeks actual sales and a target of £8000.

    Is there a way to return a value for the number of items selected in the report filter that I can then use in a calculation to multiply the target values?

    If I need to use VBA to get this then this is fine.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Return a value for items selected in a pivot filter

    how is the data pulled into the spreadsheet , if into a sheet - if its weekly you could add an additional field with the weekly target and then sum that in the pivot table so as more weeks are selected so should the target

    you could populate with a lookup for each salesman if different targets
    and if the data is in a table as the table is updated the formula should auto fill

    can you post an example sheet with dummy data - so personal info is not a public forum
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    02-14-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    7

    Re: Return a value for items selected in a pivot filter

    Hi Wayne,

    Attached is an example of the file.

    The information in the "Targets" worksheet is input manually. The information in the "Company Gross Margin Report" one is copied in from a csv export from a back office system. It is then converted/formatted etc via VBA.

    If you look at the "Summary" tab you will see the pivot that summarises the data by Consultant Name. The "Target £" field is outside of the pivot and currently looks up for the target value in the "Targets" sheet. In the "Targets" sheet column D shows the £ Target Cumulative. This is calculated as the £ Target in column C multiplied by the value in cell B2 on the "Summary" sheet. Currently the user has to manually input this value based on how many values they have selected in the pivot filter field in cell B3. What I want is to get rid of the need for them to manually input this value but somehow pick it up based on the number of values selected in B3.
    Attached Files Attached Files

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Return a value for items selected in a pivot filter

    is the consultant details entered just 1row per week
    if so then you may be able to use a table and import the csv file
    do you append to the existing data sheet or just completely import ?

    you would thne just use the week W/E filter and it will recalculate the target

    see how i changed the sheet here

    not an ideal method, i'm sure other members will have a better solution
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-14-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    7

    Re: Return a value for items selected in a pivot filter

    Hi

    Each consultant will have multiple rows of data in the "Gross Company Margin report" sheet. These rows are consolidated in the pivot to give a total value for the number of weeks selected. The consolidated value is the one that the target value compares to. In the example file you attached you can see that for the first consultant, Joe Bloggs, you have selected one week of data. This one week actually consists of 73 lines in the source data sheet and therefore your target is calculating as 73 x £3000 = £219,000. The target value for this consultant for a 1 week period is only £3000.

    The source data in the report is dropped in en masse each week with all previous data overwritten.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Return a value for items selected in a pivot filter

    but if you look at the PAY calculation thats 18K if one week selected , so i aussumed that 1 row represented 1 week , and that the consultant would be in the report many times - but only 1 row per week
    then you can put the target for each week into thelookup table and it will sum based on the weeks you choose to filter the report by

    what am i missing ?

  7. #7
    Registered User
    Join Date
    02-14-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    7

    Re: Return a value for items selected in a pivot filter

    The 18K is the sum of the pay field for 73 lines in the "Company Gross Margin report" for the w/e 07/07/13. This represents the consultants total sales for that week. The consultants target is actually a Gross Margin target so in the pivot the sum of the Gross Margin £ for that week for those 73 lines is £3.9K. His total weekly Gross Margin Target is £3K so the report should show Gross Margin £ = £3.9K, Target = £3.0K, Variance to Target = £0.9K. If he repeated exactly the same sales for 2 weeks I would expect the pivot to show Gross Margin = £7.8K, Target = £6.0K, Variance to Target = £1.8k.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Return a value for items selected in a pivot filter

    i dont think the structure of the data is going to support what I'm trying to do
    I do not understand why Joe Bloggs would correctly show 18K pay for 1 week, as he is down 73 lines , so if thats correct he worked 2,591 hours in 1 week
    so i dont think my solution is going to work with your data structure
    as with the filter on the pivot table its grouping by week and so would work if each consultant had one entry per week , which is how you data appeared to be structered , and i assumed it was just dummy data

    can you explain how Joe Bloggs works 2,591 hours in 1 week

  9. #9
    Registered User
    Join Date
    02-14-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    7

    Re: Return a value for items selected in a pivot filter

    Hi,

    Joe Bloggs is a recruitment consultant. The detailed sheets show time sheet details of all of the people who have worked for his clients. Column F shows the client which is a company. Column K shows the individual time sheet number. Columns N to P show the names of the people who have done the work. The hours relate to the workers shown on the time sheets. Joe Bloggs is targeted on the Margin earned by all of the workers allocated to him. The pivot summarises the Margin for all of these.

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Return a value for items selected in a pivot filter

    Thanks for the explanation

    This should work
    =VLOOKUP([@[Consultant Name]],Targets!$A$1:$D$3,3,FALSE)/COUNTIFS(B:B,[@[Consultant Name]],I:I,[@[W/E Date]])

    added to the table and should add the formula and also calculate when you add data or import the csv file , i would test if that works OK

    the formula does the following
    it uses vlookup to get the weekly target from the targets table

    then it counts the number of times the consultant appears for each week ending and divides the weekly target by that number
    that way
    it doesnt matter how many rows you have each week for each consultant it will simple proportion the target across the rows that apply
    Now it should work in the pivot table

    you select using the W/E filter and select the weeks you want to include

    I have not tested extensively as if the csv file does not import and include the formula - then you may need another solution

    example attached
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-14-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    7

    Re: Return a value for items selected in a pivot filter

    Hi,

    Your solution works well but the data is imported from a csv that does not contain any formulas. I could add the formula using VBA but I was hoping there was a simpler solution where I could find a value of the number of w/e dates selected in the filter list.

    Thanks for your help. I will add the formula if I can't find another solution.

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,744

    Re: Return a value for items selected in a pivot filter

    often with a table , you can import data and the formulas will autofill

    is the full data loaded or just appended ?

    I have done this quite a few times with extracted reports , years ago , although much better solutions now with powerpivot etc and easier to link to external data

+ 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. Show Multiple Items Selected in Pivot Filter
    By cp41 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2014, 06:20 AM
  2. Pivot Report Filter - Readout/Display selected items in a cell
    By FixandFoxi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-19-2012, 10:19 AM
  3. Replies: 1
    Last Post: 09-03-2012, 10:03 PM
  4. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM
  5. Display selected report filter items
    By prahlad in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-28-2009, 07:20 AM

Tags for this Thread

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