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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
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,141,722
Messages
5,708,098
Members
421,546
Latest member
delatollas

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