Change the Formula in Grand Total Column of Pivot Table

[mcormanp]

Hi,

I am building a pivot table out of data in a PowerPivot. In the grand total line in the pivot table, I would like the fields for Total Users Within Region and % of Total Users Active to calculate like they do in the Summary column below.

I have a long list of data in the power pivot where each line is a unique session, so in the pivot table I am taking the min to only get one result. But this throws off the grand total as it takes the min from the above list, hence "6". Additionally, the % of total users active column is a calculated field in the power pivot (distinctcount(name)/min(total users within region). because the total number of users grand total is wrong, its dividing 18/6 = 300%.

How do change the grand total cell for total users within region to just sum the values above it like I can do in my hand made summary below.

Thank you,


[TABLE="width: 857"]
<tbody>[TR]
[TD]Region[/TD]
[TD]Number of Sessions[/TD]
[TD]Sum of Time (Minutes)[/TD]
[TD]# of Active Users[/TD]
[TD]Total Users Within Region[/TD]
[TD]% of Total Users Active[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]6[/TD]
[TD]108[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]Central Region[/TD]
[TD]44[/TD]
[TD]623[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]86%[/TD]
[/TR]
[TR]
[TD]Executive Leadership[/TD]
[TD]12[/TD]
[TD]265[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD]NE Region[/TD]
[TD]2[/TD]
[TD]31[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]29%[/TD]
[/TR]
[TR]
[TD]SE Region[/TD]
[TD]11[/TD]
[TD]58[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]38%[/TD]
[/TR]
[TR]
[TD]Western Region[/TD]
[TD]2[/TD]
[TD]46[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]17%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]77[/TD]
[TD]1,131[/TD]
[TD]18[/TD]
[TD]6[/TD]
[TD]300%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Summary[/TD]
[TD]77[/TD]
[TD]1131[/TD]
[TD]18[/TD]
[TD]44[/TD]
[TD]41%[/TD]
[/TR]
</tbody>[/TABLE]