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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Market[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65"]Spending[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Population[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]Spending per Capita[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]% of Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65, align: right"]$100.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$20.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]67%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$150.00[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$37.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]125%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$200.00[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$33.33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]111%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65, align: right"]$450.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15[/TD]
[TD]$30.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Market[/TD]
[TD]Product[/TD]
[TD]Spending[/TD]
[TD]Population[/TD]
[TD]Spending per Capita[/TD]
[TD]% of Product Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A
[/TD]
[TD]$40[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$8.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.4%[/TD]
[TD]<- E9/sum(E9, E12, E15)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]$50[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$10.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]34.3%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[TD]$10[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$2.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]9.8%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]$50[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$12.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]30.2%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]$30[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$7.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]25.7%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]$70[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$17.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]86.1%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]$125[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$20.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]50.4%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]$70[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$11.67[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]40.0%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]$5[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$0.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]4.1%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]$450.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$30.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Market[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65"]Spending[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Population[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]Spending per Capita[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]% of Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65, align: right"]$100.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$20.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]67%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$150.00[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$37.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]125%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$200.00[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$33.33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]111%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl65, width: 65, align: right"]$450.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15[/TD]
[TD]$30.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Market[/TD]
[TD]Product[/TD]
[TD]Spending[/TD]
[TD]Population[/TD]
[TD]Spending per Capita[/TD]
[TD]% of Product Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A
[/TD]
[TD]$40[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$8.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.4%[/TD]
[TD]<- E9/sum(E9, E12, E15)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[TD]$50[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$10.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]34.3%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[TD]$10[/TD]
[TD]5[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$2.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]9.8%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]$50[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$12.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]30.2%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]$30[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$7.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]25.7%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C[/TD]
[TD]$70[/TD]
[TD]4[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$17.50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]86.1%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]$125[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$20.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]50.4%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]$70[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$11.67[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]40.0%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]$5[/TD]
[TD]6[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$0.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 121"]
<tbody>[TR]
[TD="class: xl65, width: 121, align: right"]4.1%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75, align: right"]$450.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133, align: right"]$30.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]