Custom formula for Grand Total column

Question

I have a common problem when the formula that I want to use in the Values ​​area of ​​the pivot table is different from the formula I want to use for the Grand Total column of this row. Usually I want to summarize the values, but I want to average the amounts. This is what I usually get if I rotate dates on column labels, meat types on row labels, and total orders in values.

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

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

 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, Orders are still cumulative in the afternoon, but Grand Total is now the average amount. Now I copy and paste the Pivot data onto a separate sheet, then calculate the average values. If there was a way to do this using the special Grand Total column, that would be incredible. This is one of the biggest drawbacks of Pivot Tables for me, but I hope this is due to my ignorance, which often happens. Thanks for the help!

Answer 1

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

If you are using PowerPivot V2, then this:

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

When using PowerPivot V1, this is:

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

Both do the same in that they estimate the number of rows in the table in this context, and when the meat type is “Beef,” then the temporarily filtered table has one row. If it does not have one line, then it goes along the path to AVERAGE ()

This assumes that your column headings are in the Calendar table (if you do not use a separate calendar table, then you are missing the most powerful PowerPivot IMO features). Jacob

Answer 2

I can’t come up with a “good” way, but here is one option. Add the Amount field to the data area a second time and change the operation to Medium. Then use conditional formatting to hide the average values ​​in the data area and hide the amounts in the general area.

You may find it helpful to use some array formulas in the do-it-yourelf pivot table. You lose the benefits of a pivot table, but gain more flexibility with data.