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.

1

1 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.

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy