pivot table: % of total question

jevremovj

Board Regular
Joined
Mar 9, 2002
Messages
101
Hi - I'm having trouble trying to find the correct pivot table % of total formula... I've tried % of row and % of column, and % of "store" but am not getting the results I'm trying to get.

I'm pasting a sample below... basically the formula I'm trying to fix is in yellow... for the first one, I want to know what percent the $1 Albertsons apples are of the Albertsons store total?
Book1
ABCD
3storeitemDataTotal
4albertsonsapplesSumofamount$1.00
5%ofstore?100.00%
6melonSumofamount$4.00
7%ofstore?100.00%
8orangesSumofamount$6.00
9%ofstore?100.00%
10pearsSumofamount$3.00
11%ofstore?100.00%
12albertsonsSumofamount$14.00
13albertsons%ofstore?100.00%
14ralphsapplesSumofamount$2.00
15%ofstore?200.00%
16melonSumofamount$7.00
17%ofstore?175.00%
18orangesSumofamount$5.00
19%ofstore?83.33%
20pearsSumofamount$4.00
21%ofstore?133.33%
22ralphsSumofamount$18.00
23ralphs%ofstore?128.57%
24vonsapplesSumofamount$8.00
25%ofstore?800.00%
26melonSumofamount$4.00
27%ofstore?100.00%
28orangesSumofamount$2.00
29%ofstore?33.33%
30pearsSumofamount$5.00
31%ofstore?166.67%
32vonsSumofamount$19.00
33vons%ofstore?135.71%
34TotalSumofamount$51.00
35Total%ofstore?
Sheet4
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,936
Hi,

When you are using "Show data as % of total" it gives you % of grand total and not subtotals.

I suggest you to put "Store" in PAGE field - this will give you calculation of each store separately.

See the example.

Eli
Book1
ABCDEFGH
1storeitemamountstorealbertson
2albertsonapples1
3albertsonmelon4itemDataTotal
4albertsonoranges6applesSumofamount1
5albertsonpears3Sumofamount27.14%
6ralphsapples2melonSumofamount4
7ralphsmelon7Sumofamount228.57%
8ralphsoranges5orangesSumofamount6
9ralphspears4Sumofamount242.86%
10pearsSumofamount3
11Sumofamount221.43%
12TotalSumofamount14
13TotalSumofamount2100.00%
14
data
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello to you both,

Presenting your data as follow should do the trick:
Book1
GHIJKL
3SumofTotalitem
4storeapplesmelonorangespearsGrandTotal
5albertsons7.14%28.57%42.86%21.43%100.00%
6ralphs11.11%38.89%27.78%22.22%100.00%
7vons42.11%21.05%10.53%26.32%100.00%
8GrandTotal21.57%29.41%25.49%23.53%100.00%
Sheet1


The formula used for the total is:
Sum
Show data as percent of row as the rows are the store and the column header are your product.

If you have a lot of products go the other ways:
Product on the left , store on the column header
and use
Sum
Show data as percent of column
Book1
GHIJK
3SumofTotalstore
4itemalbertsonsralphsvonsGrandTotal
5apples7.14%11.11%42.11%21.57%
6melon28.57%38.89%21.05%29.41%
7oranges42.86%27.78%10.53%25.49%
8pears21.43%22.22%26.32%23.53%
9GrandTotal100.00%100.00%100.00%100.00%
Sheet1
 

Forum statistics

Threads
1,176,388
Messages
5,902,791
Members
434,997
Latest member
bigolbearking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top