Forum Discussion
Missing values in pivot table
Hello,
Downloaded my data, inserted pivot table, report layout is tabular form. Everything looked good until I noticed some missing values where there should be values. I verified the information that the missing data that should be pulling into the pivot table is in my data table. I have tried everything I know to do to remedy the problem to no avail. Again, I have verified that the missing information is indeed in the data table, just not pulling over into the pivot table. Any suggestions are very much appreciated.
26 Replies
Actually its not missing any items.
If you look at the dataset there are three 78.91 and one 55.03
So the pivot is displaying the summary of one 78.91 and one 55.03 which is correct, however since the columnar Grand total is not being displayed it misleads you to think there are missing items when there are not. If you want to test my theory, change the two 78.91 to some other number and it will display it.
cheers
Yea_So many thanks for showing me this. I understand what is happening now, but the question now is how do I change that. I am relatively new to pivot tables and what I need to do is:-
1) have a column that shows individual amounts that are not summarised or aggregated
2) get a total at the bottom of that column.
I have attached a (much reduced) example pivot table from the sort of data I am trying to display. I can add various filters which seem to work and sort as required, but I need to display all the data and provide a total that can be cross-checked. But I am going around and around in circles trying to sort this out.
I don't wish to have the "sum of cost" column at the end, I have just included it to show I understand your point about the aggregation.
Review these charges:
11/9/2020 Igloo Energy Overcharge 11/9/2020 Igloo Energy Overcharge 11/9/2020 Igloo Energy Overcharge 11/9/2020 Igloo Energy Overcharge and ask yourself, how was I able to tell that they were different overcharges and include those differences in your dataset. For example:
is the difference overcharge number 1 from overcharge number 2
was it location?, departmental?, equipment?, Invoice/Statement? etc....
Note: Don't worry about that differentiation being displayed in your pivot table since we can convert that pivot table into cube formula type table and remove that column later on.
CSRouse What do you get when you double click the "0.00" on the second row of the pivot table (column "Sum of NetReportAmount1"?
I noticed when I formatted the pivot table in compact form the accounts missing the descriptions show up as indicated in the attached snip In this case accounts 110420 and 110846.Riny_van_Eekelen
CSRouse Difficult to diagnose on the basis of a picture alone! Can you upload a file?
- It pulls in the detail for that amount (in this case 0.00) in a separate tab. I just cannot figure out why the account description (accts rec-commercial pro fees) will not pull in for account 110420. This is not the only account that has that issue. There are 7 or 8 others.
