1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Master Detail data and Pivot Chart

Discussion in 'Discuss Data Visualizations and Charting' started by Ankush Bhatia, May 31, 2018.

  1. Ankush Bhatia

    Ankush Bhatia New Member

    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.

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    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.
  3. Ankush Bhatia

    Ankush Bhatia New Member

    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??
  4. Ankush Bhatia

    Ankush Bhatia New Member

    Here is the updated file

    Attached Files:

  5. GraH - Guido

    GraH - Guido Well-Known Member

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

    Attached Files:

Share This Page