Is Total mode for expressions possible in Pivot tables?

[Anonymous]

I saw a post on this in 2009 but wasn't clear if there is a solution, so asking again. I have a pivot table with 3 dimensions(Reason, Division and Region) Division and Region are columns and Reason in the row. The expression is avg(Avg_FirstVisit_Days) which returns my results but I would like to see an overall total.  (Total Mode in the Expression tab)Can the pivot table produce an overall total like it can in a Straight table?

[Peter_Cammaert]

Yes, but in a Pivot table it's called SubTotal because you can assign a total to every (sub)dimension. See the checkbox underneath the list of columns in Pivot Table Properties->Presentation. Create a subtotal for the leftmost dimension and you're done.

[Edit] Add Subtotals for horizontal and outer vertical dimension. At the intersection (top left or bottom right, depending on the location of the total lines/columns), you'll get an overall total.

[Anonymous]

Okay. So I know why my charts differ. I have the Straight Chart Total Mode set to Average. Can I duplicate this in a Pivot table to get a total in Avg?

[Peter_Cammaert]

A Pivot table doesn't offer the same wealth of tricks you can apply out of the box to your expression in a Staight Table like "Sum of Rows" (which is different from the "Total" that will apply the same expression to the total data set) or "Avg of Rows" or the other selections. By default it offers just the subtotals and the total of some or all subtotals.

You can however obtain the same trick by modifying your expression to include a few intelligent aggr() combinations.

Best,

Peter

[johnw · Accepted solution]

Expanding on this, in this specific case, I believe you would want to replace your current pivot table expression with this:

avg(aggr(your current pivot table expression, your pivot table dimensions))

The aggr() here is like building an undisplayed straight table with your pivot table dimensions and expression. Then you just tell it to take the avg() of those values. It will continue to work for each row in your pivot table, because each row will be evaluated in the context of the dimension values for that row, and so would basically be a 1:1 mapping to a row in the "straight table" you built with aggr(). The avg() of one value is that value, so your rows remain unchanged, and only the subtotals and totals change.

[Anonymous]

Thanks. Could you help, if I attached a sample? If so, see attached. I would like my pivot table (the bottom table) to match the straight table total by avg. Which is 172.

[Anonymous]

I got it, thanks!

[Anonymous]

I haven't solved my problem. The avg(aggr(your current pivot table expression, your pivot table dimensions))is essentially taking the averages of the averages, correct? I only want the averages of the averages for the Grand Total, like a stragiht table. The Aggr is not providing a true average for each dimension.

[johnw]

Correct.

avg(aggr(avg(Days),Reason,Region))

...gives you a true average for each cell in the pivot table, and an average of averages for the subtotals and grand total. Are you saying you also want the true average for even subtotals? OK.

if(dimensionality() or secondarydimensionality(),avg(Days),avg(aggr(avg(Days),Reason,Region)))

But for your example data, that produces the exact same result as the previous expression, so if you're still having trouble, you may want to post a new example that has data that highlights the problem.