How to set the Pivot Table Layout

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
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
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
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.
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
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.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,789
you didn't explain it properly in the first post and didn't show expected result

maybe this

pt2.jpg
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
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.
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
Thanks for your reply. What is that value in column section?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,789
add fields to appropriate area and value are you asking will add itself
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,076
Office Version
2016
Platform
Windows
I have installed the tool to capture image now. So, I can show my desired output as well as the raw data.

RAW DATA:

xl2bb.xlam
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:

xl2bb.xlam
GHIJ
17Sales ExecutiveTotal DefectivesTotal SoldDefective %
18A22
19B02
20C12
21D02
22E01
23F12
24G13
25Grand Total514
Sheet1
Cell Formulas
Range(s)Formula
J18:J24J18=H18/I18
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top