Issue in creating a pivot using two different data source with different structure.

[JainAbraham]

Hi All,

I have two sheets A and B,

A contains details like follows
[table="width: 500, class: grid, align: left"]
[tr]
[td]Alfa[/td]
[td]Beta[/td]
[td]Gamma[/td]
[td]Omega[/td]
[td]Theta[/td]
[td]Delta[/td]
[/tr]
[tr]
[td]Expert[/td]
[td]Advance[/td]
[td]N/A[/td]
[td]Beginner[/td]
[td]Awareness[/td]
[td]Advanced[/td]
[/tr]
[tr]
[td]Expert[/td]
[td]Expert[/td]
[td]Advanced[/td]
[td]Expert[/td]
[td]Expert[/td]
[td]Advanced[/td]
[/tr]
[tr]
[td]Advanced[/td]
[td]Advanced[/td]
[td]N/A[/td]
[td]Advanced[/td]
[td]Beginner[/td]
[td]Advanced[/td]
[/tr]
[/table]

and my Sheet B contains data as follows
[table="width: 500, class: grid, align: left"]
[tr]
[td]PorjName[/td]
[td]Alfa[/td]
[td]Beta[/td]
[td]Gamma[/td]
[td]Omega[/td]
[td]Theta[/td]
[td]Delta[/td]
[/tr]
[tr]
[td]ProjA[/td]
[td]Yes[/td]
[td]No[/td]
[td]Yes[/td]
[td]No[/td]
[td]Yes[/td]
[td]Yes[/td]
[/tr]
[tr]
[td]Proj B[/td]
[td]Yes[/td]
[td]Yes[/td]
[td]Yes[/td]
[td]No[/td]
[td]No[/td]
[td]No[/td]
[/tr]
[tr]
[td]Proj C[/td]
[td]Yes[/td]
[td]Yes[/td]
[td]No[/td]
[td]No[/td]
[td]Yes[/td]
[td]Yes[/td]
[/tr]

[/table]


I am trying to create a pivot where I will be using the project name as a report filter ie. Proj A, Proj B, etc.
and it should the details as how many experts, Advanced Beginner do we have for Alfa, Beta, gamma, etc

I have tried creating the pivot but some how the details are not populated correctly.


I would really appreciate if someone could point me in a direction where I could find solution to this problem

 

[Matt Allington]

Way are the rows in your first table? It just looks like random repeating values. Why is the data in the first table in columns like you have shown? Eg what is the relevance of the first row of data by column, Alpha/Expert with Beta/Advanced etc?

 

[JainAbraham]

what is the relevance of the first row of data by column, Alpha/Expert with Beta/Advanced etc?

Hi Matt,

Alfa, beta, gamma are skills and there is actually another column stating the employee name in the beginning say column A. Each employ needs to update his level of skill (i.e. is he a expert/Advanced/beginner/etc) for each skill.

I did not mention the initial column as i did not require it in my pivot. Sorry, if i was not elaborate enough.

Thanks and regards,
Jain Abraham

 

[Matt Allington]

Power Pivot is a complex beast. All tables and relationships are relevant and it is difficult to give advice without understanding the broader data model and the context. Also removing things like "skills" and replacing them with generic terms makes it so much harder to understand what you are trying to do.

Can you explain the relationship between the first table (staff skills) with the second table (project skills required or on board).

 

[JainAbraham]

Power Pivot is a complex beast. All tables and relationships are relevant and it is difficult to give advice without understanding the broader data model and the context. Also removing things like "skills" and replacing them with generic terms makes it so much harder to understand what you are trying to do.

Can you explain the relationship between the first table (staff skills) with the second table (project skills required or on board).

Thanks Matt for replying.

Okay sheet one has the information regarding the skill my company offers against the resources that I have and how competent they are in each skill.

Ill create sheet one again for better clarity

[table="width: 500, class: grid, align: left"]
[tr]
[td]Emp Name[/td]
[td]Alfa[/td]
[td]Beta[/td]
[td]Gama[/td]
[td]Omega[/td]
[td]Delta[/td]
[/tr]
[tr]
[td]Xavier[/td]
[td]Advanced[/td]
[td]Beginner[/td]
[td]NA[/td]
[td]Expert[/td]
[td]Advanced[/td]
[/tr]
[tr]
[td]Rudolf[/td]
[td]Expert[/td]
[td]Advance[/td]
[td]NA[/td]
[td]Expert[/td]
[td]Expert[/td]
[/tr]
[/table]

As you would see Xaiver is at expert level in Omega skill but only a beginner level in Alfa.
where as Rudlof is at expert level in Alfa, Omega and Delta, he has no clue what Gama is.

Sheet two:
It contains the list of projects that I have along with the various skills that are required for the project.

So in the table provided in the first post for Sheet two
my Proj A requires Skill Alfa, Gama, Theta, Delta (the rows where 'Yes' Is marked).

Now to what I need to make.

I need to make a pivot Chart and table that would base on the project show the skills that are required against the number of resources that have this skill and at what level.

I.e

If I filter ProjA then the pivot should show the various skill that are required for ProjA ( in this case it would be Alfa, Gama, Theta, Delta ) and the number of resources I have for each mentioned skill of the there levels ( i.e. Advance, Expert, beginner).

So the Pivot should look some what like the image below
I have created a random image in Paint not with the exact data I have mentioned above

Thanks and regards,
Jain Abraham

 

[JainAbraham]

the link to the final image

apparently it did not come correctly in the previous post.
looks like the image is not displaying correctly. the image is placed in the below link.

View image: Target Pivot

Thanks and regards,
Jain Abraham

 

[Matt Allington]

Here is a workbook https://www.dropbox.com/s/jml1lwxitkoscup/project skills.xlsx?dl=0

Hopefully you have Excel 2013+ with Power Query and Power Pivot.