Solved

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

Posted on 2015-02-02
11
1,759 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 51

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 51

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 51

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
 
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

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.

Question has a verified solution.

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

Suggested Solutions

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

752 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