Pivot Table: "% of" problem

[AlaskanDad]

This is complicated so I'll include some data.

I've got most of what I want just right. However, I'm trying to get the % of a total of boxes by size column and it seems to be close.

Here's a picture of the pivot table:

SIZE    VENDOR  SKU             Data                    Total
Large   1002133 D1604-2         Sum of BOXES            2,956,800
                                % of GRAND              1.14%
                                % of BOXES/SIZE         100.00%
                P0110466        Sum of BOXES            343,830
                                % of GRAND              0.13%
                                % of BOXES/SIZE         100.00%
        1002133 Sum of BOXES                            3,300,630
        1002133 % of GRAND                              1.28%
        1002133 % of BOXES/SIZE                         xxxxxx (34.01)%
        1005267 D1604-2         Sum of BOXES            5,376,000
                                % of GRAND              2.08%
                                % of BOXES/SIZE         100.00%
                P0110466        Sum of BOXES            1,028,160
                                % of GRAND              0.40%
                                % of BOXES/SIZE         100.00%
        1005267 Sum of BOXES                            6,404,160
        1005267 % of GRAND                              2.48%
        1005267 % of BOXES/SIZE                         xxxxxx (65.99)%
Large Sum of BOXES                                      9,704,790
Large % of GRAND                                        3.75%
Large Sum of BOXES                                      100.00%

Please note where I have the xxxxx's. The percentages should be the ones noted in parentheses but I can't get it there.

I've tried plodding around the "% of" formulas but it doesn't seem to work for me.

Any suggestions?

Thanks,
Rob

 

[AlaskanDad]

Are there no PivotTable masters here?

- Rob

 

[AlaskanDad]

Are there no PivotTable masters in the house?

- Rob

 

[barbola]

Can you show a sample of your data instead of the pivot?

Thanks!
Barb E.

 

[AlaskanDad]

Here is a sample:

SIZE    VENDOR  SKU      BOXES
Medium  1002133 P0110466 185,570
Medium  1002133 D1604-2  201,600
Medium  1005267 D1604-2  201,600
Large   1002133 5AA-16C  691,200

Again, my goal is to get the PivotTable to show me the % of boxes from a particular vendor of all boxes of a given size. From the example above, I would expect to see the % for vendor 1005267 of size Medium to be 34.24% (201,600/588,770)

 

[barbola]

Here is one way I did it:

Create your pivot table, but drag "Boxes" into it twice, so you have SumOfBoxes1 and SumOfBoxes2. Then in the SumOfBoxes2 column, go to it's Field Settings and click Options. Change it from Normal to % of Total.

It will give you the percentage of the total number of boxes, so what else you need to do is drag the box size field to the Page fields section.

When you view only Medium boxes, you get the proper percentage. I'm not sure how to get it to show the % of Medium boxes only with all the data showing. You would have to view each box size separately.

I hope this helps. Let me know if you have any questions.

Thanks!
Barb E.

 

[AlaskanDad]

Thanks, Barb.

That's the answer I finally came up with as well. I couldn't find a way to show % of total for everything and % of total for size on the same page.

I found that the "% of" function only referenced values above and below the current size. I'm not sure what the value of that information could be but that's what they offer.

Thanks again,
Rob