in this video I'm gonna show you how to set up this donut chart from a custom
group to pivot table from here we can see the relative proportions of employee
headcount versus pay per department grouping.
This video is part of a series
where I will take you step-by-step through the process of building this
dashboard in Excel to see the full series check out the playlist and
subscribe to see all of the videos. To check out other tutorials and reviews of
other tools please check out my channel.
In the previous video we pulled in out
data by a power query also known as get and transform in Excel 2016 applied a
top 10 item filter and adjusted a setting to stop out dashboards from
becoming distorted on refresh now in this example we used the top 10 items or
the top 10 departments the problem with this view is that it truncates
some of our data so we used to have 16,000 records and now we only have
around 13,000 so the other thing that we might want to do with this is to take
our top 10 items and then group everything else into a grouping code
other. To do that I'm going to copy this table I'm going to paste a copy over
here remove my grouping and what I'm gonna do this time is use a grouping
called group by text so I've got my top 10 items here so I'm going to take all
of these values now all of these values are in a single range if I want to
select a another value which is not part of this continuous range I can just use
the control key to say select this item as well and right click and then I can
click group now that's going to add everything to this group called Group
one let me rename this to other and hit enter I'm then going to come over to
here and collapse all of these now you notice here that what's happened to a
pivot table it's actually added additional field the field inside our
table was called Department and now I have this extra field called Department2 -
which includes this value of other we can see that over here as well
so let's go ahead and get rid of that and let's reapply a sort to this date
here the other thing I want to do to this summary is at an annual total pay
over here so I've got the sum here now you'll notice here that this value is
very big so it doesn't really display very well let's see if we can go into
the number formatting to clean this up now when formatting the numbers in the
pivot table it's really important that you come over to here right click and
click number formatting like this inside the pivot table
this formats the fields inside a pivot table as opposed to me coming over here
selecting these cells and coming over to home and using these formatting options
here what happens is if I format my data this way my formatting is only applied
to these 10 cells and if my pivot table changes in size then I'm not gonna have
the correct cells formatted so the correct way to do this is to come over
and click number format now because I have quite a large value over here what
I'm gonna do is I'm going to use a custom format and I'm gonna come up to
here this hash coma hash hash and these comma between the as a thousands
separator here now what you can do is by coming to the end of this and adding a
comma I've now turned that into thousands add another comma and that's
millions I'm going to add inside here quotes and so I know what the scale is
and click OK so now here I can see I've got two hundred twenty-nine million
let's do something similar for the head count so let's come over here number
format custom
and this time I have a smaller number so let's say I want this show as 3.9
million so I want to add a decimal place to this so to do that I can add point
zero before adding my comma and this is now three point nine thousand again
let's add in quotes okay to the end of this so that I know what the scale is
that we're dealing with let's now update some of these labels so
they're a little easier to read let's change that to headcount and let's
change that to pay let's go ahead and visualize this data using a pivot chart
and for this I'm going to use a special type of pie chart called
a doughnut and I'm going to click OK now the nice thing about this year is
we've got our head count on the inside here and we have our pay
on the outside here and what you can see from this is the proportion of head
count versus the proportion of pay so let me just add the labels on here just
make this a little easier to read so add data labels to the inside data labels to
the outside and let's just change that color to something slightly easier to
see that's better so we can see here because the inside ring is smaller than
the outside ring we can see that a smaller percentage of the population is
getting a larger percentage of the pay so let's go to this knicks chunk of the
ring over here so three and a half thousand hit count which makes up 21% of
the population is receiving 153 million which is only 18% of the pay so an
interesting piece of analysis that you can do here while we're here let us
label our charts I'm going to add chart element I'm going to add a chart
title yep I think that looks good there let us call this cost versus headcount
okay so that is using a group by text now there's two other types of groupings
that we can use inside a pivot table one of them is grouped by number and the
other one is grouped by date if you found this video helpful please remember
to give a thumbs up if you're not subscribed yet and you want to learn
tips tricks and tools to help you and your team work more effectively with
data hit the subscribe button and the notification icon so you don't miss
anything thanks for watching this video and I'll see you next time
No comments:
Post a Comment