+ Reply to Thread
Results 1 to 5 of 5

Data possibilities in Pivot Tables and Charts

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    Massachusetts
    MS-Off Ver
    Excel 2003
    Posts
    3

    Data possibilities in Pivot Tables and Charts

    Good afternoon,

    I am attempting to show distribution as a percentage of total store calls in the attached example. Distribution meaning in how many Store Calls does each Single Item UPC appear in. As you can see, there are numerous enties for each Store Call ID so there is no Row relationship between Store Calls and Single Item UPC. Is it possible to calculate the count of Single Item UPC/Store Calls and have that calculation hold true using filters of Last Name, or Retail Responsibility...

    Any tips are greatly appreciated!
    Thank You!
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Data possibilities in Pivot Tables and Charts

    It may help us to understand the problem more if you provided an example of some expected output from a specific combination of data.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    Massachusetts
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Data possibilities in Pivot Tables and Charts

    OK Andy.
    In the attachment from my original post, the Distribution tab shows distribution in the grand total row for each UPC. And that number will change based on the filters at the top, Retail Responsibility, User Last Name, ... What I would like to see is this number divided by the number of total Store Calls for the same filter, or total. So in the case of Grape, the total distibution is 84 (From the grand total row.) The total number of store calls is 129 which is a count of unique values in my data, column Store Call Response ID. So what I would like to see in a chart is 84/129 in the case of Grape. I have been able to do this if I 'hard wire' the total number of store calls into my data. The next step is getting this to continue to work once I turn on a filter. So filtering on User Last Name "Dinger" changes the distribution of Grape to 27, and the number of Store Calls to 34. I don't know how to get the pivot table to show me both, and allow me to calculate with those values.

    Thank You for your help!!!

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Data possibilities in Pivot Tables and Charts

    I can not get this done within a PT. The uniqueness of the store calls is not something that is easy to determine.

    You may have to resort to 2 PT's and some VBA code to calculate the values.

  5. #5
    Registered User
    Join Date
    04-19-2011
    Location
    Massachusetts
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Data possibilities in Pivot Tables and Charts

    I was afraid of that. Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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