Solved

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

Posted on 2015-02-02
11
1,174 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 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 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 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 40585632
Have you tried to enter the named range in your version of the file?
0
 

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 49

Assisted Solution

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

on your example the named range is not the formula

pls replace it with the formula

Regards
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 2

Accepted Solution

by:
mike247 earned 250 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 250 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 250 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now