Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
kcantor
Community Champion
Community Champion

Pareto 80/20 calculation for items making up 80% of Sales

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.

Any advice?

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])

 

 

Any Advice: @Sean or @greggyb ?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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

Pareto-Aggregate.png

and the Chart - use the Pareto % Item in the Visual Level Filters if you want...

 

Pareto-AggregateChart.png

View solution in original post

37 REPLIES 37
sheva
Frequent Visitor

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.

 

 

@Sean

 

The chart you created, does it display only the 20% of products contributing to 80% sales? 

It displays everything, up to 100%. But it is possible to identify 80% and 20%.

bomman
Frequent Visitor

Hello @Sean,

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.

 

Regards,

Todor

Hello @bomman 

 

Any news aout your issue ?

brissae
Frequent Visitor

Hello! 

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

Hi,

 

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?

kcantor
Community Champion
Community Champion

@Sean

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.

Kudos!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ThomasDay
Impactful Individual
Impactful Individual

@Sean Nice work!  

ThomasDay
Impactful Individual
Impactful Individual

@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.

Going to let the image do the talking. That worked perfectly!

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Sean,

 

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)

Sean
Community Champion
Community Champion

@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?

ThomasDay
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.