Missing values in pivot table

[CSRouse]

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.

[#2]

@CSRouse What do you get when you double click the "0.00" on the second row of the pivot table (column "Sum of NetReportAmount1"?

[#3]

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.

[#4]

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 

[#5]

@CSRouse Difficult to diagnose on the basis of a picture alone! Can you upload a file?

[#6]

@Riny_van_EekelenI have been having the same issue. I have a data table with various date/text columns and an amount column. Where there is an entry with two duplicates either in the date, the text or the amount row, these sometimes appear with blank data or missing entirely. I have included screenshots of the data table, the pivot table with the data missing and one with the text amended to make the data show. This is totally impractical for the main pivot tables as there are duplicate monthly amounts for various accounts and I am having to amend each entry in the data table to ensure they show.

I have tried to change the field settings for "Repeat item lables" for the whole table in the "Design" Tab, but this doesn't work.

[#7]

@Windward On the Report Layout, first select Tabular, then Repeat item Labels. Does that help?

[#8]

@Riny_van_Eekelen  Sorry. These are already ticked in the field settings box. I have tried this in the design/report layout tab for the whole table, but this just shows the year and week duplicated (which I don't want) and not the missing values. I have attached a copy of the workbook so you can see.

[#9]

@Windward I opened your file, chose Tabular first then Repeat.

All columns are "filled". See attached.

[#10]

@Windward By the way, what an awkward pivot table, using the Total Amount field as one of the row headers. 

[#11]

@Windward 

For these two fields setting Repeat item label is not set. 

If only for one field to set

I didn't catch what an idea of such PivotTable which aggregates nothing, in attached file it's kept as it is with only one field changed.

[#12]

@Riny_van_Eekelen 

Hi again. I have already tried your suggestion and in your illustration the pivot table is actually missing two entries of £78.91 on 9/11/21 for Igloo Energy. You can see in the "transactions" data there are 4 entries on the same date. Two of these are the missing ones as there is both duplicate text and a duplicate amount. If I change the text in some small way they appear.

The column "Total Amount" is actually mis-labelled and should just be "Amount" as it is not a total or aggregated data. Again this can be seen in the tab "Transactions" Data in my example.

[#13]

@Sergei Baklan I don't want the "Year", "Week No." or "Date" Columns to include "Repeat data in rows" as this is difficult to read with multiple date values in a large table. The attached workbook is an abridged version only.

[#14]

@Windward 

Sorry, misunderstood the question.

As for same rows, PivotTable doesn't show exactly the same in Rows, it aggregates data for them in Values.

[#15]

@CSRouse 

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

[#16]

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

[#17]

@Windward 

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.

[#18]

@Yea_So 

If I understand you correctly I could change each text to "Igloo Overcharge Jan", "Igloo Overcharge Feb" etc. However, is there no way to have a column of values with the same identifier, but without the values being aggregated and how do I get a total at the bottom?

"Cube formula type table" .....??? Way over my head I am afraid.

[#19]

@Windward 

any column fields you put in the pivot row field gets aggregated if the labels are the same so if you put the amounts in the values field you can subtotal the From Account column/Field

Re: Cubes formula:

in a classic pivot table you cannot convert a pivot table into a cubes table using OLAP:

However, if you add your dataset to the data model you can use that tool to convert it into a cubes formula:

and you can rearrange columns to your hearts desire 

or delete columns 

cheers

[#20]

@Windward 

You can subtotal them by date (not sure if that date is a report date or transaction date)

but it seems to work:

you can change the Reconciliation date to probably Vendor/Client Provided Statement Date