Custom Formula for Grand Total column

Question

I have a frequent problem where the formula I want to use in the Values area in my Pivot-Table is different than the formula I want to use for the Grand Total column of that row. I typically want to Sum the Values but I want to average the Sums. Here is what I normally would get if I pivoted the dates on the Column Labels, Meat Type on the Row Labels, and Sum Orders in the Values.

Row Lables | Day 1 | Day 2 | Day 3 | Grand Total
________________________________________________
Beef       |  100  |  105  |  102  |   307
Chicken    |  200  |  201  |  202  |   603

I get sums by day and a sum of all of the days in the Grand Total column. Here is what I want to have:

Row Lables | Day 1 | Day 2 | Day 3 | Grand Total (Avg of Day Totals)
________________________________________________
Beef       |  100  |  105  |  102  |   102.3
Chicken    |  200  |  201  |  202  |   201.0

In this case the Orders are still summed by day but the Grand Total is now an average of the sums. What I do now is copy and paste the Pivot data onto a seperate sheet then calculate the averages. If there was a way to do this with a custom Grand Total column it would be incredible. This is one of the biggest shortcomings of Pivot Tables for me but I'm hoping it is due to my ignorance, which it often is. Thanks for the help!

Answer 1

You can write a measure that checks the number of 'rows' in a particular filter context and nest that in an IF() to determine which to use.

If using PowerPivot V2 then it's:

=IF(HASONEVALUE(Calendar[Day]), SUM(AMOUNT), AVERAGE(AMOUNT))

If using PowerPivot V1 it's:

=IF(COUNTROWS(Calendar[Day])=1, SUM(AMOUNT), AVERAGE(AMOUNT))

Both do the same thing in that they assess the number of rows in the table in the given context and when the Meat Type is 'Beef' then the temporarily filtered table has one row. If it doesn't have one row then its going down the path of the AVERAGE()

This assumes your column headers 'Days' are in a table called Calendar (if you aren't using a separate Calendar table then you are missing the most powerful functionality of PowerPivot IMO). Jacob

Answer 2

I can't think of a "good" way, but here's one option. Add the Amount field to the data area a second time and change the operation to Average. Then use conditional formatting to hide the averages in the data area and hide the sums in the total area.

You might be better off just using some array formulas in a do-it-yourelf pivot table. You lose the pivot table benefits, but get more flexibility with the data.