Master Detail data and Pivot Chart

[Ankush Bhatia]

Hi All,

I have attached a sample sheet in which I have 2 sheets (Position Management, Detail). For each position we can have multiple interviews done. I am trying to create a pivottable wherein I present a summary view. Basically, for each positions (Lets say 8), how many profiles are in which stage?

I have depicted a sample report in view sheet but not sure how to achieve it.

 

[GraH - Guido]

Hi Ankush,

Not a lot of data in your file to go on and the sample you have provided seems incomplete. At least not all scenarios (stages) are present.
The view sheets only contains some titles. Not making explicitly clear what needs to be in the content.
How are both sheets linked? Seems to be Request ID, is that correct?

Do you need a pivot? a simple (calculated) data table or a chart? Which chart then?
Did you try some COUNTIF(s)? Like for each stage-RequestID combo?

Can you please clarify a bit more. More data would help.

 

[Ankush Bhatia]

Yes Requirement id is field. I need a pivot table. I created a data table, generated relationships and drew pivot on top of it and it worked but I am stuck on another part.

If you look closely, I have 2 sheets. Position and detail. Both have req id is field [1-> many relationship].

I am trying to create a query to check if for said position we do have any detail record? If so update a column in position sheet. I tried vlookup but it seems not working

Any clues??

 

[Ankush Bhatia]

Here is the updated file

 

[GraH - Guido]

That is looking a bit better, yes.

Since you are looking for an exact match, use VLOOKUP with the false argument at the end.
But then again, you now use a data model (PowerPivot). Why would you need VLOOKUP? The data model takes care of that for you.
Looking at the model you made, the only thing you need from the details is the Profile Status.

So let's add some columns (N -> X) in the sheet Position Management:
Interview Scheduled; Submitted; Withdraw; Profile Rejected; Dropped; No Response; No Submission; On Hold; Onboarded; Selected; Rejected

I assume - from the drop down in column C in sheet Req Details - these are the only values used in Profile Status.

Then in cell N2 use this formula:
=COUNTIFS(Detail[[Req ID]:[Req ID]],Position[@[Req ID]:[Req ID]],Detail[[Profile Status]:[Profile Status]],Position[[#Headers],[Interview Scheduled]])

Drag to the right.
Done.
As suggested in my first reply, doable with COUNTIFS, no need for using vlookup nor a data model, or maybe I still do not get it.

Please have a peak in the attached file, if it is workable for you.