Powerpivot calculation based on fields in pivot

[mc_laura]

Hello,

I was wondering if it is possible to have a column in calculate based on the fields that are included. For example, if only market is available, I want the % of per capita spending to be divided by the total across all markets, but if I include market and product, have the % spending to be the % of product spending. I know that if I move around the columns I will get what I'm after, but the customer is adamant about having market as the leftmost column.

Thanks!

See example below.

Market	Spending <tbody> </tbody>	Population <tbody> </tbody>	Spending per Capita <tbody> </tbody>	% of Total <tbody> </tbody>
1	$100.00 <tbody> </tbody>	5	$20.00 <tbody> </tbody>	67% <tbody> </tbody>
2	$150.00	4	$37.50 <tbody> </tbody>	125% <tbody> </tbody>
3	$200.00	6	$33.33 <tbody> </tbody>	111% <tbody> </tbody>
Total	$450.00 <tbody> </tbody>	15	$30.00
Market	Product	Spending	Population	Spending per Capita	% of Product Total
1	A	$40	5	$8.00 <tbody> </tbody>	19.4%	<- E9/sum(E9, E12, E15)
1	B	$50	5	$10.00 <tbody> </tbody>	34.3% <tbody> </tbody>
1	C	$10	5	$2.00 <tbody> </tbody>	9.8% <tbody> </tbody>
2	A	$50	4	$12.50 <tbody> </tbody>	30.2% <tbody> </tbody>
2	B	$30	4	$7.50 <tbody> </tbody>	25.7% <tbody> </tbody>
2	C	$70	4	$17.50 <tbody> </tbody>	86.1% <tbody> </tbody>
3	A	$125	6	$20.83 <tbody> </tbody>	50.4% <tbody> </tbody>
3	B	$70	6	$11.67 <tbody> </tbody>	40.0% <tbody> </tbody>
3	C	$5	6	$0.83 <tbody> </tbody>	4.1% <tbody> </tbody>
Total		$450.00 <tbody> </tbody>	15 <tbody> </tbody>	$30.00 <tbody> </tbody>

<tbody>
</tbody>