Find 'BEST PERFORMING' products with a Pareto Chart (80/20 Rule) | Power BI

so you need to show what products make up x amount of total sales so you can get an idea of like what the 20 of products end up making up roughly around 80 of your actual total sales be it in a table or chart well I'm going to show you just by using a few Dax measures so that in mind let's jump over to my power bi desktop so if you want to follow along I've got a link to the data set in the description below so once you've got that downloaded then we can head over so all you need to do is head over to get data and then in this case the file that I'm going to be using is a CSV I have to do is click on that and then transform the data and because I've just imported a CSV some of the dates have gone a little bit weird so what I can do is just quickly transform these to use local under change type so when you do this all you have to do is just go date and then select the format that it's coming out as and as is United States just do English United States do okay and then it will convert it to the date format whatever you have set in your power bi so now we have that sorted we can just import this data set because every thing else done all these other changes it was just that date was a bit off to do closed load and apply and once we got that loaded in all I want to do is just quickly create a table do a drop in the information and then we want to just put in subcategory remove that Visual and then total sales but I'll create a measure for that first so let's just create a measure for total sales total sales and then all you're going to do is just create a sum and then we just want to click sales which is there and then save that let's give that comma to break up a thousand drop that in and then we've got that information let's click out of there let's do touch sales and top of the bottom so we can see the most and that down a bit and you see oh there we go and now we've got our total sales let's add in a graph now we want to just go with a simple graph with line so we want to basically a bar so we can see the sales amount so if we put a sub category on it as the x-axis and then we put toes of sales as our column y-axis and then we can see how that's looking in the same information there and now we have that information what we want to be able to do is understand of what level this is ranking so basically we know we've just sorted this in this order but we want it to know based on total sales which one's got the most we can visually see it here so we need to be able to tell the measures what we're creating based on a ranking system of where it's going and the way to do a rank is if we just create a measure and call it rank we want to be able to look at this whole table and then create what is basically its own subtable that will then give us the ranking of one two three four five six seven eight and ten and the way you do that is by using rank X which is like I say because we're creating a table X's are always table based and then within there we want to be able to look at all the amount but to do that we need to create a filter removal of subcategory so we've got subcategory down here because that's what we're looking at here and if we add that in that's now removed what that whole information is and then it'll be able to work out a percentage within this to be able to say of total sales so that measure that we just created total sales we now have our rank of it so basically what it's doing is pulling all the data that is here so it's basically giving you this amount and then it's working out how much does that make of this how much does that make of this and make of this and then just sticks them in an order so if we add this rank now to the table we can see it goes one two three four and it's all in that order and it doesn't matter if we change this it's still going to do it in order look phones are still there and everything if we do it the other way it would do it the other way it won't matter so now it's actually got your rank and it's also just a useful measure to know anyway because it's good to know where you got to rank and especially if you want to do like a top end you wanted to be able to pull about like if you wanted to filter by the top five you could use the rank to be able to create a filter where you can just see the top five just where it comes in really handy but the key thing here is this allows us to now get a running total because we're now looking at this we want to get a running total where it adds this to this and then that amount would be there so then that would be what just under 66 660 000 and then to add the next bit and then that amount would be there and it'll just keep showing you the running total so to do the running total I just need to create a new measure let's just call this running total sales technically I should put subcategory because we're using subcategory as the main thing here but because we're just doing it as a one-off if you're doing multiple ones it'd be good to name it differently but for now let's just do that and then we just want to put in calculate because we want to be able to then create the whole filtering function and what's going to happen here is we want to look at the total sales we then want to be able to use the ranking we know we don't want to do a b hey how it does this there we go and no don't do that let's do that there we go and now yeah we want to add in Dax function top end and then we want to use our rank so we just type in rank and then what that's going to do is basically each time it's going to look at oh stop bench and go under but the only way that's going to work is if we then force it to then not look at each line separately we want to undo the filter so just like we did with the rank we need to now add all to subcategory again so if we go under there and then do all and then type in sub category there it is there we go and then we close bracket and then a comma and then all this because it's still within top n that we're doing this we want this to relate to what we're looking at which is total South so we type in total sales again and then now we finish that let's just return the close bracket and then close bracket again because that's the close bracket kit for the top end and the next closed bracket for the calculator save that let's give it a comma break and then if we drop that in we can now see as I mentioned how that is that amount and then that amount is that and that amount and then that amount is all that amount and then did it and so on and so forth and it goes all the way up and then you've got your final list with gone there and then don't worry about subtotals the the totals are just giving you what the first rank is it's a rank here right there because it's top end and so it's always going with the first one so it's giving you results so this information would say if you're going to use a table probably might be a good idea to maybe just remove that because it might confuse people but for this example doesn't matter so now we've got that what we might want to do is let's move rank to here and then what we want to be able to do is then look at our percentages but what I need is to be able to see how much percent this takes up of I'm gonna need this number here to be applied across everything and as we keep using all I'm going to be using all again so all we do is create a new measure and I'm going to call this one total sales all because it's basically going to look at the final number so total of sales and then we're in Brackets all so we don't get confused and then we're just going to just do calculate and then all sub category and it would help I actually put in total sales first there we go and then it's just simple case of just doing a close bracket and then we've got our information and then we can do that and then if we dump this in now we should have this number all across here and we do so that's great so now we can create a measure where all we're going to do is just divide that to that and then that will give your cumulative percentage so then we can see by this point how much does this take up of this and you can visually see it's more than 50 but that's only because you quickly eyeballing it but if you wanted to see it clearly and then also add it to this chart this is where we need to add in that extra measure so we got this measure here and because we're using total sales let's just call this percent total sales cumulative oh I just limited oh I saw that right and then oh no I've got you I think cumulative yeah and that's right and then yeah so what we need to do now is do a divide and the Divide is going to be our running total so we do running we'll do sales comma and then we want to do it by our total sales all because we want the full number which is this bit here so if we type in all that will pop up and then just to make sure there's no zero or we just do zeros if there's a blanks we add in zero and then we just do that and then we can add in our percentage and then you can have your decimal places I'm actually just going to do this as zero decimal places because it can just get a little bit messy and then if we just drop this in we can see now what takes up at which point so in essence of those 17 items we know the top three make up about 40 we're looking at about what makes up 80 is just under or probably at half so instead of it being a 2080 it's a 50 80 but it gives you an idea of these if you want to get to focus on these ones sell the most and make up the most and then these are all just extras that just happen the time spent on these might not be as beneficial as these because these are driving up more sales and that's where that comes in really handy now that's great if you want to see it in a table now if you want to see it in here then what we can do is drop in under the line y-axis there we go that we now have a line that shows you when it gets to 100 and then if we just format it where's data label there you are and then we've got a data labels I don't like that like that so let's do values display units above that must be under options there we go position line there we go above and now that's done and let's say if you didn't want those amounts as you can probably format those a bit better this detail style position column inside and just inside Center yeah it's not very great so let's just put it back to Auto and then if we just format it to thousands bit better and we could do zero decimal places I guess it went into the millions and then you probably want to have it on auto or just set it to millions and now we have our lovely little chart and also our table here that can show you how much of your sales make up x amount of percent all in a nice visual so as you can see it only took a few measures to be able to do this and technically you could have done it all within one if you were just going to use variables but obviously I split it out to make it so you could see each step visually to be able to do it and it works well within a table as you can see here and then also within a graph to just give you that Insight now if you needed to do anything else outside of this so say if you dropped in a different header then you will get an issue so if we drop in say um product name yeah so if we change that to product ID it will then break because all the alls have been relating to subcategory so if you want to have a different View and look at different ones technically you can create other measures that will allow you to do that and if you wanted it in the same chart you could probably get a way of doing that if you did a fill perimeter and but then you have to say that you need to change this value and then this column for it to work or you could use bookmarks so you could just create one that would change for bookmarks and then different people can look at different bits but for using it as it is it's just a good visual just a base on like one set thing at that time as your focus and can bullet across multiple different views so best to sort of understand what do people want to see and then just apply it to that graph maybe do two if Max I don't think you probably need to do more than that otherwise it can get confusing so I hope you found this video useful and if you did please give a like And subscribe and if you want to carry on critical Journey check out these videos over here and as always until next time foreign

Channel: Dean Chereden

Views: 5,450

Keywords: Analytics, BI, DAX, Data, Data Science, Excel Users, Fun, Power BI, Power Query, data analytics, how to use power bi, introduction to power bi, learn power bi, microsoft power bi, power bi certification, power bi course, power bi dashboard, power bi dax, power bi demo, power bi for beginners, power bi full course, power bi pro, power bi training, power bi tutorial, power bi tutorial for beginners, what is power bi, webscrape, excel

Id: sgQyS8vEshw

Length: 12min 11sec (731 seconds)

Published: Thu May 18 2023