Grand totalling measures?

[Russ Skinner]

Is there any easy way to add a grand total (Product A+B+C by year) to a powerpivot such as the one below which updates when measures are added/removed from product list.

Selecting 'grand total on for columns' in pivot table design menu does nothing.

The following pivot contains (3) measure Product A sales, Product B sales, Product C sales from three independent tables linked to a calendar which provides the year columns and row quarters.

Thanks,

Russ

 

[iyyi]

If the first Total line is in row 20, you can use the following formula for B20
=SUMIF($A$2:$A$19,SUBSTITUTE($A20,"Total ",""),$B$2:$B$19)
Copy the formula down and to the right

 

[Jacob Barnett]

Russ,

You may have to resort to a 4th measure, [Product A Sales] + [Product B Sales] + [Product C Sales]. If all you want is an extra total row at the bottom then you will need to use a 'Set' to screen out the total measure for the quarters.

Jacob

 

[Russ Skinner]

Thanks for the replies.

4th measure looks like the best option, but downside is this is hard coded to sum all products even if one is removed from values section of the pivot.

Not sure what you mean by the use of a 'Set' Jacob, but I'll see if I can hunt down an example.

Russ