cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcantor Super Contributor
Super Contributor

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 Datanaut!




1 ACCEPTED SOLUTION

Accepted Solutions
Sean Super Contributor
Super Contributor

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

@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

33 REPLIES 33
Sean Super Contributor
Super Contributor

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

@kcantor I'll look at this later. Have the feeling it will take some time...

Super User
Super User

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

For a static analysis, this pattern is much easier: http://www.daxpatterns.com/abc-classification/

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




kcantor Super Contributor
Super Contributor

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

I can do a static analysis. A pareto has been requested by the PTB.



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

Proud to be a Datanaut!




Super User
Super User

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

Just to make sure that we don't misunderstand: Pareto-Analysis is a sub-group of ABC-Analysis (actually, an AB-analysis: https://en.wikipedia.org/wiki/ABC_analysis)

 

The pattern I've posted is the standard-one (static), just simpler than the source you've used.

Then there's also a dynamic one: http://www.daxpatterns.com/abc-classification-dynamic/

There the user can slice years or product groups or whatever to play around further.

 

You can also do it using M-code:

http://www.poweredsolutions.co/2015/06/15/abc-analysis-with-power-query/

or here: https://social.technet.microsoft.com/Forums/en-US/a1e63005-ead7-46a4-8b48-6f42fd3973fd/runnig-total-...

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Sean Super Contributor
Super Contributor

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

@kcantor I'm gonna try this out... let's see if it crashes

 

Can you provide some sample data? Or at least give more of an idea what the tables look like?

 

Sales Performance                           Item Table   

LineType     LineSales                        Code

 

Thanks!

Super User
Super User

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

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Sean Super Contributor
Super Contributor

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

Sorry it took so long to answer. Had not done Pareto Charts before.

 

However, I don't seem to experience any problems with this. I don't have blank rows but I don't think this would be an issue...

 

I'm running the March 2016 64-bit PBI on Win 10 Pro with 16GB RAM.

 

@thomasday I know you try to push the limits of PBI - have you had any problems creating Pareto Charts with your data sets?

 

also found this post (Pareto taking too long)

http://community.powerbi.com/t5/Desktop/Pareto-80-20-calculation-taking-too-long/m-p/24195/highlight...

 

EDIT: Noticed one thing in your formulas...

Pareto.png

You are adding a filter only to the Overall Total => This should push your line over 100%

I don't know what you data table looks like but it seems this would result

in filtering values only from the Denominator but those same values will be included in the Numerator

Pareto2.png

Super User
Super User

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

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)

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ThomasDay Established Member
Established Member

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

Hey @Sean I've not really gotten into the visualization side of the world...I'm working my way to it.  I have used visualizations for massive exports--and so I do know they can get tangled--and when they do I always got a message "too much data--may use sampling" or some such thing.  At which point all bets were off--could be blanks or many identical rows.

 

That said, it only happened when one of the measures was actively ranking other measures in the visualization---making it complicated in that the formula had a "RANKX" variable in it like you have --so it was doing that calc for every row of data points in the column--i.e. running a calc for each one that involved every other data row.  So THAT's an efficiency problem and potentially a memory issue esp when the visualization itself is data heavy.  Here, there are RANKX, SUMX, and TOPN--all of which probably make internal work tables to do the calcs and who knows when a visualization clears them out--so it's not like using a calc column, query, or some such thing.

 

Anyway, that's the best I can do here but if your files are BIG, perhaps the lesson is "if there's a sort that can happen--or pre-ranking of some sort--then perhaps you can avoid the calc heavy rankx AND the SUMX part".  This is consistent with @ImkeF's suggestions.

Tom

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)