Help me with a noob Excel Power View thing?
February 27, 2015 4:44 PM Subscribe
I've recently discovered Excel's Power View plugin and I'm naive enough to think it can solve a problem I have. I've watched this pie chart video (about halfway down). I can't make it do the 'partially shaded slices' thing in the video at 3:23. Help?
Let's say there are Divisions. Divisions have Teams. Teams have Projects. Projects have a binary status - they're done or they're not.
So I set up a table for each:
DivisionID, DivisionName.
TeamID, TeamName, DivisionID.
ProjectID, ProjectName, TeamID, Status.
Status is a 1 (complete) or 0 (ongoing).
What I'd like it do do is make a pie chart with a colour for each Division. Inside those colours is a slice for each Team. The shading for each slice should show what proportion of that team's projects are complete.
I seem to be able to do the colours and the slices. (It seems to know that each division has a different number of projects because the colours are different sizes [eg two Divisions have five teams, but the sizes taken up by their colour 'wedges' are very different.] I'm hoping this is because it knows the number of projects is somehow linked, but I really have no idea.)
The next part - making each slice show the proportion of ongoing and completed projects - is witchcraft.
I think this is because I have the last bit backwards. In the video, they click a Country that has Medals, and I guess this means each Medal has a Sport. I think to get the same result I'd need to click a Status. But this would mean that Statuses have Projects which have Teams with have Divisions and now my nose is bleeding.
Thanks!
Let's say there are Divisions. Divisions have Teams. Teams have Projects. Projects have a binary status - they're done or they're not.
So I set up a table for each:
DivisionID, DivisionName.
TeamID, TeamName, DivisionID.
ProjectID, ProjectName, TeamID, Status.
Status is a 1 (complete) or 0 (ongoing).
What I'd like it do do is make a pie chart with a colour for each Division. Inside those colours is a slice for each Team. The shading for each slice should show what proportion of that team's projects are complete.
I seem to be able to do the colours and the slices. (It seems to know that each division has a different number of projects because the colours are different sizes [eg two Divisions have five teams, but the sizes taken up by their colour 'wedges' are very different.] I'm hoping this is because it knows the number of projects is somehow linked, but I really have no idea.)
The next part - making each slice show the proportion of ongoing and completed projects - is witchcraft.
I think this is because I have the last bit backwards. In the video, they click a Country that has Medals, and I guess this means each Medal has a Sport. I think to get the same result I'd need to click a Status. But this would mean that Statuses have Projects which have Teams with have Divisions and now my nose is bleeding.
Thanks!
Response by poster: So I worked it out, mostly:
- new clustered column chart with ProjectID and StatusID
- select Chart in the filter pane
- set StatusID to 1
- manually select all of the project bars in the clustered column chart (if there's a shortcut to select them all at a click, I can't find it)
The result - a pie chart that shows which teams and divisions have lots of projects and which have a few, and gives a sense of overall progress by division and team.
posted by obiwanwasabi at 1:55 AM on February 28, 2015
- new clustered column chart with ProjectID and StatusID
- select Chart in the filter pane
- set StatusID to 1
- manually select all of the project bars in the clustered column chart (if there's a shortcut to select them all at a click, I can't find it)
The result - a pie chart that shows which teams and divisions have lots of projects and which have a few, and gives a sense of overall progress by division and team.
posted by obiwanwasabi at 1:55 AM on February 28, 2015
This thread is closed to new comments.
- my Size is 'Count of ProjectID'
- my Color is 'DivisionID'
- my Slice is 'TeamID'
If I create a filter for StatusID, and change it to 1, 0 or both, the pie chart does indeed change, and if I hover my mouse over a slice, it accurately reports the number of Projects with that status. I just want it to do this with the fancy partial shading animation so I can see what's done and what isn't at the same time.
posted by obiwanwasabi at 6:34 PM on February 27, 2015