Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2487
  • Last Modified:

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

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
Marcia Morris
Asked:
Marcia Morris
  • 4
  • 4
  • 3
6 Solutions
 
Rgonzo1971Commented:
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
 
Marcia MorrisAuthor Commented:
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
 
Rgonzo1971Commented:
Have you tried to enter the named range in your version of the file?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Marcia MorrisAuthor Commented:
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
 
Rgonzo1971Commented:
Hi,

on your example the named range is not the formula

pls replace it with the formula

Regards
0
 
mike247Commented:
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
 
Marcia MorrisAuthor Commented:
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
 
mike247Commented:
I uploaded it again.  What version of Excel are you using?
C--Users-michaels-Desktop-Copy-of-Order-
0
 
mike247Commented:
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
 
Marcia MorrisAuthor Commented:
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
 
mike247Commented:
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now