I am sure this has been brought up before. However, I have looked for hours on the internet and here:
Attached is data and pivot to explain my issue. I have also added a column 'SHOULD BE' and placed the values that I am expecting to get.
I am trying to get the pivot table to subtotal and show the avg of the items by day. However, the pivot calculates the subtotal average based on the raw data, not on the pivot table data. For example:
DAY ITEM AVG VALUE SHOULD BE Monday Widget 1 1.33 Monday Widget 2 1.00 Monday Widget 3 4.00 Monday Total 1.80 2.11
The pivot averages as 1.80 when I would like it to average these 3 values to then equal 2.11.
Any help would be appreciated. Thank you.
Bookmarks