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
Marcia MorrisProject ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mike247Connect With a Mentor Commented:
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
 
Rgonzo1971Connect With a Mentor Commented:
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 MorrisProject ManagerAuthor 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
Rgonzo1971Connect With a Mentor Commented:
Have you tried to enter the named range in your version of the file?
0
 
Marcia MorrisProject ManagerAuthor 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
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

on your example the named range is not the formula

pls replace it with the formula

Regards
0
 
Marcia MorrisProject ManagerAuthor 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
 
mike247Connect With a Mentor Commented:
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 MorrisProject ManagerAuthor 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
 
mike247Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.