?
Solved

Why is my PivotChart not picking up all data for my chart?

Posted on 2015-02-02
11
Medium Priority
?
2,789 Views
Last Modified: 2015-02-04
Hello! As I was double checking my order count using the filter option vs. what the Pivot count is, they are not matching up. When I double click on the line item in the Pivot Data for the Jan FP (B8) I get a count of 25 orders and the RP (B9) gives me a count of 226 orders. BUT when I filter the Source Data by 'order date' (column C), then Type (column G) I get counts of 29 & 259 respectively.

I can't figure out why the counts don't match up and the chart that is shown in the 'chart' tab has the counts from the Pivot and I just realized the weren't matching up. Can you please let me know how to fix this or let me know what I'm doing wrong.

Also..is there a shortcut to count the number of rows when you use the filter option so that the user doesn't have to scroll all the way down to the end of the document to see what the last row is? It becomes extremely difficult when you have thousands of rows.

I haven't updated the counts in the chart as of yet as I would like to understand why my counts aren't matching up.

Thanks in advance.
Order-Tracking-Matrix-020215.xlsm
0
Comment
Question by:Marcia Morris
  • 4
  • 4
  • 3
11 Comments
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 total points
ID: 40585578
Hi,

if you go in PivotTable Tools Option Data Change Data Source you will see your range is fixed

You could create a named range with a dynamic array

=OFFSET('SOURCE DATA'!$A$1,0,0,COUNTA('SOURCE DATA'!$A:$A),13)

like in my example

Regards
Order-Tracking-Matrix-020215V1.xlsm
0
 

Author Comment

by:Marcia Morris
ID: 40585599
Rgonzo1971,,, thank you, the counts match. But when I tried to refresh  got an error the following error message Error Message. This will prevent me from updating the source data with updated data.
0
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 total points
ID: 40585632
Have you tried to enter the named range in your version of the file?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Marcia Morris
ID: 40585683
Rgonzo1971 I named the range (Source_Data) in my version of the spreadsheet but now the 'order date' has disappeared from my Pivot table options which resulted in the chart disappearing as well. I don't know what i wrong.
Order-Tracking-Matrix-020215-V2.xlsm
0
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 total points
ID: 40585708
Hi,

on your example the named range is not the formula

pls replace it with the formula

Regards
0
 
LVL 2

Accepted Solution

by:
mike247 earned 1000 total points
ID: 40586465
When I click on your values, all of them are set to "average".  If you are trying to count rows, you will need to use "count".  If trying to sum, you need "sum".  See if the attached file is what you are after.
C--Users-michaels-Desktop-Copy-of-Order-
0
 

Author Comment

by:Marcia Morris
ID: 40586957
Hi mike247... I couldn't open your attachment. Also, the values are set to average because I am taking the average of some of the data. And I am manually including the count of orders as a text box in the chart.
0
 
LVL 2

Expert Comment

by:mike247
ID: 40586971
I uploaded it again.  What version of Excel are you using?
C--Users-michaels-Desktop-Copy-of-Order-
0
 
LVL 2

Assisted Solution

by:mike247
mike247 earned 1000 total points
ID: 40587013
I'm not sure what is going on with the upload of my file.  When I tried to download it, I had to "save as" and then manually change the file extension back to "xlsm" (what it was originally) so that it would recognize it as an Excel file.
0
 

Author Comment

by:Marcia Morris
ID: 40587297
mike247....I was able to open it based on adding the file extension as you indicated and I am using 2013. I checked the counts and the Pivot is picking up all the rows correctly... thank you.

Do you know what I was doing wrong? Want to know for next time since this spreadsheet will be used to include the February counts for both FP & RP.
0
 
LVL 2

Assisted Solution

by:mike247
mike247 earned 1000 total points
ID: 40587347
You needed to have the "Order Number" field  in the "values" section of the pivot table and summarize it by "count" (not "sum" since there are no values to sum).  This will count the number of orders.  Then you needed "Type RP/FP" as your column.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question