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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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