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

Pareto chart: error in cumulative percentage

Hi,

 

I'm trying to create a Pareto chart, but I'm facing an issue with the calculation of the cumulative percentage.

 

My data looks like:

PowerBI_1.JPG

 

I want to built a pareto chart with the total stop time per reasonas a bar chart, and the cumulative percentage per reason as a line chart.

 

I first built a rank column: Rank = RANKX(ALL(FactsStopReasons[Reason]);[TotalStopTime])

 

This gives me:

PowerBI_2.JPG

 

As you can see, the rank has an equal value when the total stop time is equal.

 

This results in:

 

PowerBI_3.JPG

 

The bar chart is correct, but the cumulative view is not correct. The cumulative view doesn't work when the rank is equal.

 

My cumulative sum has following formula:

CumulativeStopTime = SUMX(TOPN([Rank];ALL(FactsStopReasons[Reason]);[TotalStopTime];DESC);[TotalStopTime]) 

Amount_All = CALCULATE([TotalStopTime]; ALL(FactsStopReasons[Reason]))

Pareto % = DIVIDE([CumulativeStopTime]; [Amount_All]; BLANK())

 

This is all working fine, also when I apply filters etc. But when values are equal, and as a consequence also the rank, my cumulative % is not correct.

 

I already tried to calculate a rank in power query, but this will limit me in the filters afterwards. It needs to be calculated on the fly in my opinion.

 

Who has any idea?

 

Many thanks for the feedback!

 

Regards,

Hans

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Pareto chart: error in cumulative percentage

Hi @HansBa'

I made a test but can't reproduce the rank as yours, could you point out what's wrong with me?

8.png

 

Best Regards

Maggie

HansBa Frequent Visitor
Frequent Visitor

Re: Pareto chart: error in cumulative percentage

Hi Maggie,

 

thanks for your reply!

 

In my table with the rank, the value for reason 90 are grouped. So the total StopTime for reason 90 = 11. The rank is based on the aggregated value.

 

Regards,

Hans

Helpful resources

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 354 members 3,688 guests
Please welcome our newest community members: