How to set the Pivot Table Layout

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,585
Office Version
  1. 365
Platform
  1. Windows
As shown in below Table. "F" has sold 2 items out of which one is defective. i.e. 50% of what he has sold is defective. How do i represent this in Pivot Table?
I am unable to figure out the Layout.

Raw Data:

Sales ExecutiveProducts SoldDefective
FXYZNO
DXYZNO
FXYZYES
CXYZYES
AXYZYES
CXYZNO
BXYZNO
BXYZNO
DXYZNO
AXYZYES
GXYZYES
GXYZNO
GXYZNO
EXYZNO

I tried as shown below, but I get it as 100% for all the Sales executives. Please suggest as I am definitely missing something.

My Pivot:

Pivot image.png
 

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"
Please let me know if I need to change my Data layout in case the required output is not possible with my current layout. Also, if the question is not clear enough, please let me know.
I want to see the Percentage of Defective items. Please assist.
 
Upvote 0
No Sandy666. thanks for the try but that is not the desired output.
Ideally it should be 50% for "F" because he has sold two products out of which 1 is defective. hence (1 / 2 *100 = 50%)
Similarly, "A" has sold 2 products and both are defective, so the Defective Item % would be 100% for him and not 14.29% as reflecting in your sample.
 
Upvote 0
you didn't explain it properly in the first post and didn't show expected result

maybe this

pt2.jpg
 
Upvote 0
This is also a good way to show the sales executives the percentage of Defective items sold. Could you guide me how you did that?. Thanks in advance. I'll check your reply tomorrow morning.
Good night.
 
Upvote 0
Thanks for your reply. What is that value in column section?
 
Upvote 0
add fields to appropriate area and value are you asking will add itself
 
Upvote 0
I have installed the tool to capture image now. So, I can show my desired output as well as the raw data.

RAW DATA:

Book1
ABC
1Sales ExecutiveProducts SoldDefective
2FXYZNO
3DXYZNO
4FXYZYES
5CXYZYES
6AXYZYES
7CXYZNO
8BXYZNO
9BXYZNO
10DXYZNO
11AXYZYES
12GXYZYES
13GXYZNO
14GXYZNO
15EXYZNO
Sheet1


Below is the desired Output needed in Pivot:

Book1
GHIJ
17Sales ExecutiveTotal DefectivesTotal SoldDefective %
18A22100%
19B020%
20C1250%
21D020%
22E010%
23F1250%
24G1333%
25Grand Total514
Sheet1
Cell Formulas
RangeFormula
J18:J24J18=H18/I18
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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