+ Reply to Thread
Results 1 to 21 of 21

Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

  1. #1
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    In the attached spreadsheet, you'll notice that my pivot chart is plotting the same number more than once, because the numbers appear more than once in the field list which it is pulling from.

    The data source is a column containing hundreds of the numbers you see plotted, but I only want an average of each. After going through all of the numbers in the original set of data, there are no inconsistencies that I can find (i.e., nothing is misspelled, there aren't any spaces before or after the numbers).

    What could be causing the field list to populate some of the numbers more than once? Is it a problem with the original data or with the pivot table/chart?
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Hi,

    Some of your part numbers are stored as actual numbers, but most are stored as text. That's why you see separate entries in the list - they all need to be either text or numbers.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    your Parent Part No has half values as Text, and half as Numbers so PT cannot eliminate duplicates

    ptnutex.jpg
    Last edited by sandy666; 09-11-2017 at 10:27 AM. Reason: img added

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    You also have "WiFi" and "Blank" included in your row labels in your pivot table. Filter them out.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Thanks for the feedback and mark thread solved

  6. #6
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Quote Originally Posted by sandy666 View Post
    Thanks for the feedback and mark thread solved

    I converted every column from text to numbers using these instructions:
    1. Select one column of cells that contain the text.
    2. On the Data menu, click Text to Columns.
    3. Under Original data type, click Delimited, and click Next.
    4. Under Delimiters, click to select the Tab check box, and click Next.
    5. Under Column data format, click General.
    6. Click Advanced and make any appropriate settings for the Decimal separator and Thousands separator. Click OK.
    7. Click Finish.

    However, the problem still exists and I am getting duplicate values in the pivot field list and chart. Likewise, when I am viewing my spreadsheet the numbers are all left aligned unlike the view point you seem to have where some are right aligned.

    Am I missing something here?

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Enough
    • Select one column of cells that contain the text.
    • On the Data menu, click Text to Columns.
    • Under Original data type, click Delimited
    • Finish

    Attach example, desensitized excel file with source data
    probably enough two cols of source: Parent Part No & Total Time

    or try this way:
    1. click on PT
    2. PivotTable Tools - Design
    3. Report Layout - Show in Tabular Form
    4. Report Layout - Don't repeat Item Labels

    You've different time for the same parent so it looks like duplicates
    for:
    3040160 - 05:55
    3040160 - 04:39
    Last edited by sandy666; 09-11-2017 at 05:13 PM.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Did you refresh the pivot table after amending the data?

  9. #9
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Quote Originally Posted by sandy666 View Post
    Enough
    • Select one column of cells that contain the text.
    • On the Data menu, click Text to Columns.
    • Under Original data type, click Delimited
    • Finish

    Attach example, desensitized excel file with source data
    probably enough two cols of source: Parent Part No & Total Time

    or try this way:
    1. click on PT
    2. PivotTable Tools - Design
    3. Report Layout - Show in Tabular Form
    4. Report Layout - Don't repeat Item Labels

    You've different time for the same parent so it looks like duplicates
    for:
    3040160 - 05:55
    3040160 - 04:39
    I tried both suggestions and neither is working for me. And for some reason when I try to upload another spreadsheet the upload fails.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Maybe your second file was too big? only 1 MB for xlsx/xlsm, more for xlsb

    filesize.jpg
    Last edited by sandy666; 09-12-2017 at 01:59 PM.

  11. #11
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Quote Originally Posted by xlnitwit View Post
    Did you refresh the pivot table after amending the data?
    Yes I have refreshed several times.

  12. #12
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Quote Originally Posted by sandy666 View Post
    Maybe your second file was too big? only 1 MB for xlsx/xlsm, more for xlsb
    I converted the file to xlsb and still same issue. Is it because I've already uploaded a spreadsheet to this thread? If there is another way to get the file uploaded please let me know. I've tried three web browsers and multiple file formats now but same issue.

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    • turn off all "blockers" in your browser and try again
    • try upload source file and result file as two files, or
    • try any sharing server, eg. TinyUpload and paste link here to your file

    what is the file size?

    or
    ask Admin/Mod why you cannot upload your file(s)
    Last edited by sandy666; 09-12-2017 at 02:20 PM.

  14. #14
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    I'll try tinyupload.com when I get home from work, as I do not have access to it here.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    No prob, but I hope your file will not be 100 MB

  16. #16
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Quote Originally Posted by sandy666 View Post
    No prob, but I hope your file will not be 100 MB
    Check the tinyupload.com file athttp://http://s000.tinyupload.com/index.php?file_id=07220957755131193008

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Compare two workbooks, one from here and one from TinyUpload

  18. #18
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Your spreadsheet is correct, but what did you do to fix it?

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    select column A on data sheet then Clear formats
    • Home - Editing - Clear - Clear Formats
    all numbers should be aligned to the right side
    if not, select range of all numbers and use Text2Columns:
    • Data - Text to Columns - Delimited - Finish
    then refresh PT (not sheet or workbook but just PivotTable)
    • Right click on PivotTable and from menu select Refresh
    Last edited by sandy666; 09-13-2017 at 10:02 AM.

  20. #20
    Registered User
    Join Date
    06-11-2015
    Location
    NE US
    MS-Off Ver
    2013
    Posts
    70

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Thank you!

  21. #21
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Chart is duplicating certain values that I add to the Axis Fields List, why?

    Welcome

    don't use any formatting until your result is correct

+ 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: 0
    Last Post: 09-07-2017, 05:02 AM
  2. [SOLVED] filtering a pivot chart / pivot table on x-axis values
    By Trebor777 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-20-2016, 01:28 PM
  3. Pivot chart stacked, axis in values labels in percentage
    By rpinxt in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-01-2016, 03:42 PM
  4. negative y axis on pivot chart but axis doesn't change
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 06-24-2015, 02:19 PM
  5. Drop Down list to select Chart x axis values
    By rlg50 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-07-2013, 11:52 AM
  6. filtering axis values on a pivot chart
    By jrosko1 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-25-2013, 02:53 PM
  7. Replies: 0
    Last Post: 07-13-2005, 05:05 PM

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