cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThunderRoad Frequent Visitor
Frequent Visitor

Cummulative Percentage with filter for Pareto chart

(Link to the pbix: Pareto.pbix)

 

Hi. I've been struggling with this for a while. I've read a lot of answers and blogs but neither of those addressed an issue I'm having. The data: 

 

pareto4.PNGData

The thing is, I need to filter this in the report with a couple of slicers (by Group and Date) so I CAN'T rank the defects in power query or with a calculated column (depending on the slicer I'll have to deal with different data).

 

Now, I've got this:

pareto2.PNGSlicer

Using these measures:

 

  • Cumm = CALCULATE(SUM(Table1[Quantity]), FILTER(VALUES(Table1[Defects]), RANKX(ALL(Table1), SUM(Table1[Quantity]), , DESC)))

 

  • Total = CALCULATE(SUM(Table1[Quantity]), ALLSELECTED(Table1))

 

  • Perc = 100 * DIVIDE([Cumm], [Total], 0)

 

  • Rank = RANKX(ALLSELECTED(Table1[Defects]), [Cumm], , DESC)

 

Could someone help me to get the cummulative percentage for the pareto? The end result would be this:

pareto22.pngCummulative with slicer

pareto3.PNGPareto

 

Thanks.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
kcantor Super Contributor
Super Contributor

Re: Cummulative Percentage with filter for Pareto chart

@ThunderRoad

This can help you get what you need. I had the help of some friends to complete this but I now have three functioning paretos that can be filtered, cross filtered, and sliced/diced anyway I need it.

https://community.powerbi.com/t5/Desktop/Pareto-80-20-calculation-for-items-making-up-80-of-Sales/m-...



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

Proud to be a Datanaut!




View solution in original post

Highlighted
ThunderRoad Frequent Visitor
Frequent Visitor

Re: Cummulative Percentage with filter for Pareto chart

Man I don't know how I didn't find that post...

 

It worked great! I didn't use your accepted solution though (didn't want to use SUMMARIZE, my table is much smaller anyways). I used this solution.

 

The new measures I used:

 

  • Total_Amount = SUMX(Table1, Table1[Quantity])

 

  • Rank_All = RANKX(ALL(Table1[Defects]), [Total_Amount])

 

  • Pareto_Value = SUMX(TOPN([Rank_All], ALL(Table1[Defects]), [Total_Amount]), [Total_Amount])

 

  • Amount_All = CALCULATE([Total_Amount], ALL(Table1[Defects]))

 

  • Pareto_% = DIVIDE([Pareto_Value], [Amount_All], BLANK())

 

 

pareto5.PNGyay!

Thanks a lot for the help @kcantor.

 

 

 

View solution in original post

3 REPLIES 3
kcantor Super Contributor
Super Contributor

Re: Cummulative Percentage with filter for Pareto chart

@ThunderRoad

This can help you get what you need. I had the help of some friends to complete this but I now have three functioning paretos that can be filtered, cross filtered, and sliced/diced anyway I need it.

https://community.powerbi.com/t5/Desktop/Pareto-80-20-calculation-for-items-making-up-80-of-Sales/m-...



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

Proud to be a Datanaut!




View solution in original post

Highlighted
ThunderRoad Frequent Visitor
Frequent Visitor

Re: Cummulative Percentage with filter for Pareto chart

Man I don't know how I didn't find that post...

 

It worked great! I didn't use your accepted solution though (didn't want to use SUMMARIZE, my table is much smaller anyways). I used this solution.

 

The new measures I used:

 

  • Total_Amount = SUMX(Table1, Table1[Quantity])

 

  • Rank_All = RANKX(ALL(Table1[Defects]), [Total_Amount])

 

  • Pareto_Value = SUMX(TOPN([Rank_All], ALL(Table1[Defects]), [Total_Amount]), [Total_Amount])

 

  • Amount_All = CALCULATE([Total_Amount], ALL(Table1[Defects]))

 

  • Pareto_% = DIVIDE([Pareto_Value], [Amount_All], BLANK())

 

 

pareto5.PNGyay!

Thanks a lot for the help @kcantor.

 

 

 

View solution in original post

Oscar_Mtz_V Frequent Visitor
Frequent Visitor

Re: Cummulative Percentage with filter for Pareto chart

You Rock! Thanks.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,499 guests
Please welcome our newest community members: