cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

I used this as a foundation : http://dutchdatadude.com/power-bi-pro-tip-pareto-analysis-with-dax/

Total Amount= SUM(SalesPerformance[LineSales])

Item Rank=RANKX(ALLNOBLANKROW('Item'[Code]), [Total Amount])

Pareto Value Item=SUMX(TOPN([Item Rank], ALLNOBLANKROW('Item'[Code]), [Total Amount]), [Total Amount])

Any Advice: @Sean or @greggyb ?

Proud to be a Super User!

1 ACCEPTED SOLUTION
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

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

37 REPLIES 37
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.

Post Patron

@Sean

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

Helper I

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

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

Frequent Visitor

Hello @bomman

Any news aout your issue ?

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

New Member

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.

Helper I

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?

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!

Proud to be a Super User!

Impactful Individual

@Sean Nice work!

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.

Community Champion

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

Proud to be a Super User!

Frequent Visitor

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)

Community Champion

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

Frequent Visitor

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?

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

Super User

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!