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!
posted by obiwanwasabi to Computers & Internet (2 answers total)
 
More info if it helps:

- 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


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


« Older No, really, I like writing...   |   Trip cancellation etiquette amongst friends Newer »
This thread is closed to new comments.