Forum Discussion
Missing values in pivot table
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!
The question was: Re: Missing values in pivot table
The answer is:
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. Yes and to not put it in the Row pivot field so they won't go missing.
Does that not answer the question?
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