Find 'BEST PERFORMING' products with a Pareto Chart (80/20 Rule) | Power BI
Vložit
- čas přidán 30. 07. 2024
- In this video, I show you can see which products make up the most percentage (%) of total sales, Pareto Principle, to visually show the best-performing products, all with a few DAX measures.
#powerbi #dax #pareto #8020
📁 Dataset -
data.world/deanchereden/super...
🔖c h a p t e r s 🔖
00:00 - Intro
00:25 - Import dataset
00:37 - Convert dates
01:14 - Create measures
01:19 - Total sales
02:27 - Rank
04:30 - Running total
07:00 - Total sales (all)
07:55 - % Total sales cumulative
09:26 - Graph view
💌 My email - dean@deanchereden.com
🌍 My website - www.deanchereden.com
🐦 Twitter - / deanchereden
🎵 Vhsceral - Gimetime chll.to/dd82e17f
🎶 Listen to Chillhop Music - • Misha & Jussi Halme - ...
Hello Dean,
I have watched lots of video guide for the pareto still not able to understand. It was really great the way you split the each measure to help understand it. Thank you :)
Awesome video, I've watched it a few times. I have a question though. I have a list of users and the number of items purchased. I need to know the top X% of users (meaning, those who have bought the most items) and their X% of items purchased. For example, the top 10% of users purchased 40% of items. Since there are so many overlaps in items purchased, I needed to use a custom tiebreaker in order for RANKX to work such that there are no ties. But now, whenever I use the ALL function, the output uses the old ranking system with ties. I think I'm a little confused overall and would appreciate your guidance. Thanks!
Thank you for your video! I am trying to do the running total, but instead of a categorical sum, I did a categorical average, so now it's doing a running average. Do you know know to force a running total after a catetgorical average?
I've not tried a running total that is not sum based so would need to play around testing it with something like average.
You'll expect it would do an average of the combined groups but might sum the averages or do an average of an average. Will test with min and max too as would be a useful thing too.
Hello Dean. Great video, thank you. I have one problem though - when i add an additional row (category) to he matrix, it messes up the ranking. I have categories and sub-categories and dont know how to rank from 1 in a given category...
Hey Michal, this is linked to the ALL section of the rank measure. Because only sub category is being used on the visual, any additional columns will need to be added in ALL so as an example, if you add category on top of sub category then your rank measure will be:
Rank =
RANKX (
ALL ( 'Power BI Sales Dataset 80 20 Graph'[Sub-Category],
'Power BI Sales Dataset 80 20 Graph'[Category]),
[Total Sales]
)
@@DeanChereden Thanks a milion Dean! Much appreciated. I finally grasped the logic here.
You're welcome 🙂
Hey Dean! Do you have a video that does a pareto but say the chart will only show the Top 10 Sku’s and that all other SKU will be shown as others?
Hey, I don't but I had done some dax years ago where you could use a whatif slicer to select top X and everything else gets bucketed as 'other'.
I've managed to find the DAX I did but need to see if I can get it to work with the Pareto layout because it wasn't cumulative.
Will aim to either get it to work and do a video or make a video of the DAX I have at least for my next video 🙂
Hi there thanks a lot for the video. It worked the only issue is i added a slicer for suppliers and when i select multiple suppliers at once, the cumulative line doesn't seem to work as it should. It goes up and down instead of steadily up. Please assist? I am even open to getting on a quick 10 minute call with you to show you on screen please. I need to fix it for my client deliverable asap
Hope the message I replied to on linkedin helped 🙂
Thank you for the video !!!
Would you know a measure that returns the rank that reaches 20% of the [% Total Sales Cumulative] ? in this case, the 20% sales is reached by rank 2
Hi, I think what you need is covered in a video Injae Park did as that used % to give the result. Here's a link to the video czcams.com/video/UWxaUT6HUvo/video.htmlsi=l80DtqnsjRorLpbu or just search Power BI Park for his channel
Really useful vedio. I am having a problem though- when I put the rank measure, I am having a blank row with rank numbers and sales amount but product name blank. Can you help!
Normally this happens when there is no data then creating a measure that includes blanks.
For the rank measure, in the visualization section you added the rank measure to, click on the down arrow next to the rank measure name on the measure in there and check if 'Show items with no data' is not ticked.
This being ticked will show extra blank lines if no data normally.
@DeanChereden Thanks for taking your time to reply.
Great video, unfortunately this solution does not work for my data as there are too many distinct items.
Sometimes when you have a large amount of items you might be able to see some interesting things.
I did with Twitter accounts using a hashtag and found of the 4k accounts that posted, using the hashtag, 250 posted more than 50% of tweets.
Or, if you can, create groups for your items so that reduces the number of distinct items 🙂
Thankyou for the video,just one request plz remove the background music .its distributing