I have tried this several different ways and always end up crashing my program. I am in need of guidance.
I have a Sales Performance Table that lists my total sales for the company. this is related to the Item Table by the Code field. I would like to create a visual that shows which items (by code) that make up 80% of the total sales. As all of my calculation attemps have resulted in a crash, I am not sure which to include here as I am 7 attemps in and all of those measures and fields are still in the data set.
I used this as a foundation : http://dutchdatadude.com/power-bi-pro-tip-pareto-analysis-with-dax/
The measures I adapted are:
Total Amount= SUM(SalesPerformance[LineSales])
Amount All Products=CALCULATE([Total Amount], ALLNOBLANKROW('Item'[Code]), SalesPerformance[LineType]="Item")
Item Rank=RANKX(ALLNOBLANKROW('Item'[Code]), [Total Amount])
Pareto%Item=DIVIDE([ParetoValueItem], [Amount All Products], BLANK())
Pareto Value Item=SUMX(TOPN([Item Rank], ALLNOBLANKROW('Item'[Code]), [Total Amount]), [Total Amount])
Solved! Go to Solution.
@kcantor So I think we got it => follow the pictures and let me know if this works for you too...
Start by => clicking New Table => enter the formula => this will create the first 2 columns
=> then create the other 4 Calculated COLUMNS => 5 and 6 are the same (just wanted to see if one will be faster than the other)
=> then create the chart instantenously!!! @ThomasDay
and the Chart - use the Pareto % Item in the Visual Level Filters if you want...
Hey @Sean and thanks for great solution for pareto chart.
Can I do "Pareto Total per item" without creating a new table?
My data contains date and some other information that I want to use as filter and when using your solution and I lost that information making new table.
I implemented your solution. It works fine but i need to add a filter by year and month.
I tried 2 solutions:
1, Adding these 2 columns - which means that the number of records is increased and the pareto analysis doesn't work.
2. Connected 2 tables by ItemNo (in your example) - without any filtering everything is ok. When I try to filter it by date something breaks. I came to the conclusion that the problem is in the second column ("Total per Item No")
I would be really happy, if you can help me.
Could this work under filters?
For example: If we have 4 countries and we need to know the 80/20 for each of them, and we add a filter for this result with the information as is, would this work? I am Trying to understand this result under other circumstances
Thanks in advance
did you already find a solution for it? I also need to re-calculate the pareto after adding filters. The explained way is only showing the numbers without re-calculation.
Thanks in advance.
I'm trying to do the part of the cumulative month to month, but it is getting ordered by value and not a month. They know tell me how to sort by month?
That is a thing of beauty. I opened up the link today and was getting ready to give it a try when I was notified of this message. I worked through each of the steps and 'voila', it worked instantly and perfectly. Thank you so much for helping me with this. Now I am going to go back to the original link and try to understand the how and the why.
@kcantor I think the data may also be saying it's not 80/20 but a little more concentrated...85/15 maybe. Anyway, that makes me wonder why you wouldn't do the whole pareto now that the computational grunt work is pulled out to cols using @Sean's pattern and you wouldn't have to explain those things and just let the data speak to you.
As you requested, I'm running my report on a Windows 7, 16GB i7 notebook. Power BI version 2.33.4337.281 64-bit (March 2016)
@timsleeper Thanks for replying.
How many COLUMNS (items) did you have in the Pareto Chart that was taking forever?
Seeems total ROWS is not that important at all but COLUMNS the items you'll actually plot.
Because for each COLUMN it has to iterate over the entire data set...
I don't need to make a pareto chart as the ones shown previously on this topic. I just need to classify each product on a table as 80/20 or 20/80. Then I'll get other info related to each product such as inventory, profit margin, average weekly sales, etc...
The number of items to be classified are around 4.000 to 5.000. I believe that's what you meant by number of columns on the chart right?
This really is a classic problem, isn't it. The powerbi stack is so good at sucking in numbers that some visualizations get overwhelmed. It would be nice to have a granularity parameter on the chart... Tom
Performance for the dynamic patterns (using measures) can be improved if you aggregate your fact table accordingly.
If you need to maintain it unaggregated for other purposes, it could make sense to create an aggregated one just for this purpose. (Using the query-editor or calculated table in DAX like here: http://www.sqlbi.com/articles/transition-matrix-using-calculated-tables/ - this article also giving some arguments why this suggestion might make sense - dynamic segmentation shares much logic with the pareto-analysis)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Click here to read more about the October 2021 Release!
Explore the latest tools,training sessions,technical expertise, networking and more.
Mark your calendars and join us for our next Power BI Dev Camp!