Only Show Grand Total For One Field, subtotal and grand total for other

Hi

I have a pivot table which has site & product group in Rows, week commencing & date ordered in columns and Tonnes Ordered in the values section. This works fine as the pivot is showing tonnes ordered for each site, each product group, each day with totals for the week (as it should).

I now want to work out what my average tonnes ordered is for each product for the whole month (not each day, not each week), I envisage a column right at the end saying average. I have created a formula which totals the tonnes per product group and divides the number of days gone in the month. When I insert this formula into values, it shows me the average for each day, each week and a "grand total" at the end.

I understand why it does this, however I only want a grand total column at the end that shows the average per product group. I know I can remove sub totals, but this will then remove the totals for the tonnage (which I still want to keep).

Is there anyway I can do the above?

Hope this makes sense, Kind Regards.

Hi Rory

Thank you for taking the time out to reply. It's clear I have posed my question incorrectly / without any detailed explanation. What you have provided is really good to know, however, doesn't really work in this scenario (this is because I didn't explain thoroughly what it is I am trying to do). As our pivot table grows throughout the month, the column labels will expand and from what I can see, I will have to keep editing the set as we go along in the month. I have attached a basic example now and this should make things clear.

I have my week number and a date within that week in columns, customer and product type in rows and the tonnage sold in values. I now create a measure called average (which at the moment is sum of tonnage / distinct count date), however this is incorrect because if we are on WD 6 but sold a product on only 5 days, it will divide by 5 rather than the 6 we want. Ps we don't need anything complicated to work out WD gone in the month as we would only sell Monday to Friday hence why the counting of dates.

I then want the average to only show at the end (so in the example I don't require the red columns but only the yellow at the end). With sets, I would have to keep editing the set and removing the columns I don't need as the pivot expands.

Hope this makes sense and apologies for not explaining in length the first time round, however thank you for the above, it is useful to know

attachment