How can you create an automatic control chart
in Power BI and first of all what is a control chart and how can you use it to continuously
improve any given process I'll cover all that and more and if you have use control chart
either in Power BI or excel do leave me a comment and let me know what you think about
them and if you would like to become a Power BI Pro make sure to click subscribe and click
the bell so you're notified whenever I go live to answer your Power BI questions.
Now whenever I think of control charts I think
of bowling. Now, I grew up in India playing cricket so bowling had a totally different
meaning there so here in the bowling lanes I'm actually not that good so all I tried
to do is I try to mark that central line and I try to bowl right down the middle now of
course I do have the gutters on both sides and let's call them the control lines or controlled
limits shall we. So I have them on both sides and I'm trying to bowl as close to the central
line as I can and try to stay within those control lines the upper and lower control
lines and that really is control chart. Not if you were to plot out where I end up bowling
over time over the different frames I am all over the map. So, this as a process is not
in control it's not stable is very variable and if you would look at a controlled process
a stable process that would be a very tightly all the observations would be tightly around
the central line so that's what that would be a process in control now so how do we go
about creating these the center line and the control line what the central line is calculated
as an average so we take an average of a prior period and say Hey that is the the the process
location and that's the kind of the center line for us and then the upper and lower control
lines are determined using sigma or the standard deviation so the upper control line and lower
control line are typically plus minus three sigma although they don't have to they can
be tighter plus minus two sigma plus minus one sigma would be a much tighter indicating
that the process is is very finely tuned and you can stay within a very narrow range but
typically plus minus three sigma is the default so lets go into Power BI and check it out.
So here we are in Power BI and the process to be a focus on right now is shipment from
a warehouses and the measure that we have selected to build a control chart around. Is days to ship. Now we could have chosen
any different measure we could have chosen I don't know how many defective boxes like
count of defective boxes per one thousand shipment or we could have chosen how many
how many specific orders we ship late how many of them will meet shipments. It all depends
on what your goal is with the process which aspect of the process you're trying to prove
in this case it is days to ship so this is the average days to ship by the month year
now and you can see it varies this goes up and down as time progresses. Now we want to
build a control line here based on the prior year's numbers and for that I do have a formula
defined and if you look at the formula it is simply takes the same measure that we are
plotting right here average to ship and says yep give that to me but give that to me for
the last full year and that is accomplished by using the parallel period function. So
you can see here I'm showing a kind of the average days to ship for 2016 is this and
the central line we're just using the average days to ship from the prior year as kind of
a a key indicator that hey this is what we're aiming for so let's plot that on here so that
we have a central line. Now for the upper and lower control limit the first thing we
need to calculate is the standard deviation. Now I already have standard deviation here
and I want to show that you really quick so just like we have the average days to ship
which goes up and down we have, let me bring
in the standard deviation here so you can see standard deviation it goes up and down
as time goes on but we need we need a static value a straight line based on the full prior
years to the same way we had calculated status center line were gonna add the standard
deviation control line if it's gonna let me do it.
There we go! Right and the formula is almost
exactly the same as what we have for the control line so give me standard deviation but give
it to me for the entire last year that's what parallel period does. Now that we have the
standard deviation the upper and lower control limits are simple the upper control to make
is going to be actually so it's going to be at the center line plus three sigma, sigma
being the stand deviation and the lower control limit is similarly going to be.
A central line minus three sigma. I messed
up but you you get the idea. So now let's put this on the scrap this was just too damn
pretty one so I'm gonna delete that and I'm gonna add the upper control limit and the
lower control limit so there you have it now in our case in in case of adventure works
they are actually operating really well so you can see that the process is well within
the plus minus three sigma so maybe what we can do is is set the bar higher and you can
set the bar higher by putting these control lines closer together by making them plus
minus one sigma's instead of three over here I'm just gonna change that to you know plus
minus one sigma of that is I change the lower control limit and I'm gonna change the upper
control limit.
And now I have a much tighter much higher bar to meet I have to stay within these bowling
lanes remember. Now here is where things get magical not if you have checked out my Power
BI tutorial or your inside my Power BI course you've heard me talk about it that dax measures
are magical they have the magic of define once used everywhere and by the way you can
find a link to do to do the tutorial either the cards are down in the description. So
what are we talking about well let's bring on the slicers my friend so here we do find
the the control limits we define the the upper lower control limits and central line but
with those measures them being dax measures you can slice and dice and change them any
which way you want and it's going to all gonna update automatically. So this is for our overall
data but here we have orders ship by country and all I have to do is click on United States
and as a filter just do that click on Australia and it shows me just the you know soon automatically
calculates based on the center line to be the ability standard deviation just forestry
that said this is the control chart just for Australia how the process has been performing
in Australia and similarly I can go to Canada United Kingdom France I was actually looks
good Germany to maybe Canada United Kingdom seems to be some trouble there and of course
again the magic of DAX define once used everywhere you can slice and dice it any which way you
want including categories so I can say in UK show me bikes show me mountain bikes or
road bikes and I can see exactly how that process is working for my selection. Another
thing that I like to do using control charts is show them as small multiples which just
means showing the same graph repeated so here you can see United States Canada United Kingdom
Australia their all kind of shown here and if you can fit you can show other countries
as well and you can see just by a glance that how controlled the process are so first of
all the first thing I would look at is how wide these lines are the wider they are the
less stable are in control the process. Speaker 0:
Is so you can see the United Kingdom lines are pretty tight so that means coming in so
do you remember these lines are based on last year at least in our model so we came in with
a very tight and stable process in Canada was was tight as what United States an Austrian
not so much but of course as you can see with the points outside the control limits here
that pretty much every territorial struggles again give you a very nice easy picture with
very little effort. Now you can take things further with Power BI control charts you can
define the center line based on perhaps the prior month so you can look at an month by
month and use prior month you can define the center line based on a rolling period maybe
at thirty sixty ninety did one period or out three or six months rolling period and besides
that you can highlight the out of control observations to imagine the ones that are
outside the control limits these ones an example or maybe highlighted in red or something like
that you could do that and you can also build the moving range chart which often accompanies
the control chart. If you would like to learn how to create dax measures and build Power
BI a models like the one I showed then check out my Power BI tutorial it's in the cards
and link in the description and if you get stuck and have any questions join me on my
live talk Power BI show on Fridays. Until next time, power on my friend.
Hey keep watching more videos and keep learning
Power BI but if you did enjoy this video I would love to hear from you so leave a comment
like subscribe all the good stuff power on my friends.