I am not sure if this type of question was posted here before because I myself am not sure how to ask it. So here goes, I have a massive three-dimensional dataset that looks somewhat like the table below, but with 10 "Offers" and around 40000 "Persons" rows with several hundreds of "Companies".
| Person | Company | Offer 1 | Offer 2 | Offer 3 |
|---|---|---|---|---|
| Person 1 | Company 1 | Rejected | Accepted | On hold |
| Person 2 | Company 1 | Accepted | Rejected | Accepted |
| Person 3 | Company 1 | On hold | On hold | Rejected |
| Person 4 | Company 2 | Accepted | Rejected | Accepted |
| Person 5 | Company 2 | Accepted | Rejected | Rejected |
| Person 6 | Company 2 | On hold | Rejected | On hold |
| Person 7 | Company 3 | Rejected | Accepted | Rejected |
| Person 8 | Company 3 | Accepted | Accepted | Accepted |
| Person 9 | Company 4 | On hold | Accepted | Accepted |
| Person 10 | Company 5 | Rejected | On hold | Accepted |
| Person 11 | Company 5 | Accepted | Rejected | On hold |
| Person 12 | Company 5 | Accepted | Rejected | On hold |
What I need is a (Pivot) table that shows how many people from every company has Accepted the offer. How so much ever I try, what I get is this here, that is, the table only shows the person that has accepted ALL three offers. Is there any way for the Pivot table columns to show OR instead of AND? My ideal solution would be this table here. Is this possible? Is this because there are no numerical data in the table?
P.S: I understand I can use COUNTIF but I would also need the table to be dynamic (knowing who actually accepted the offer by clicking on the number, or add another row below each company to know who the people are, etc. P.P.S: Yes, I've tried using "Distinct Count" and I have filtered the Offers by "Accepted" within the Pivottable options dialog.
Hope you super users can help me out here? I will be much obliged! TIA!
Thank you.
11 Answer
Please check whether the following information is helful to you.
Select your table and add it to PowerPivot.
Create 3 measures for "Offer1", "Offer2" and "Offer3".
Offer1:=CALCULATE(COUNT(Table1[Offer 1]),FILTER(Table1,Table1[Offer 1]="Accepted"))
Offer2:=CALCULATE(COUNT(Table1[Offer 2]),FILTER(Table1,Table1[Offer 2]="Accepted"))
Offer3:=CALCULATE(COUNT(Table1[Offer 3]),FILTER(Table1,Table1[Offer 3]="Accepted"))
Then create a pivot table. You can select the tabe, go to
Design > Layout > Subtotals > Show all subtotals at top of group.Please note, you need some settings if the layout of pivot table does not meet you needs, please go to
Design > Layout > Report Layout, change its layout.
