Forum Discussion
Missing values in pivot table
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.
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.
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
Yea_So Hi Many thanks. I think I have all the answers now but to summarise:-
If I want to list items individually I need to ensure that they have unique identifiers in the text otherwise the amounts will be aggregated.
To get a grand total I need to have the "cost" column as a "Value" i.e. Sum of Cost (but I can relabel this as say "Amount")
I think I have got it now so thanks again. I don't think I can mark this as Answered as this was not my question originally.
I have attached an amended example to confirm I have understood.........I think!
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