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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top