Pareto chart not closing to 100%

[o59393]

Hi all

I am trying to a pareto table with a column called requirement and its respective count of times they appear:

First thing I do is the rank dax:

Rank Kore requirement = 
RANKX (
    CALCULATETABLE ( DISTINCT ( GAO[kore_requirement_code] ), ALLSELECTED ( gao ) ),
    CALCULATE(COUNT(GAO[kore_requirement_code])),
    ,
    DESC,
    DENSE
)

Then, the cumulative and total:

Cumulative kore requirement = SUMX(
TOPN([Rank Kore requirement],
ALL(GAO[kore_requirement_code]),[kore requirement dax],DESC),[kore requirement dax])

Finally, the pareto:

The result is:

As you see in the last image, the pareto percentage reached up to 49.82%

how can i get it right?

Thanks.

[v-lili6-msft · Accepted solution]

hi @o59393 

When you  create the rank measure, just use Skip instead of DENSE in the formual as below:

Rank Kore requirement =
RANKX (
CALCULATETABLE ( DISTINCT ( GAO[kore_requirement_code] ), ALLSELECTED ( gao ) ),
CALCULATE(COUNT(GAO[kore_requirement_code])),
,
DESC,
DENSE
)

Rank Kore requirement = 
RANKX (
CALCULATETABLE ( DISTINCT ( GAO[kore_requirement_code] ), ALLSELECTED ( gao ) ),
CALCULATE(COUNT(GAO[kore_requirement_code])),
,
DESC,

Skip
)

Regards,

Lin

[o59393]

hi @v-lili6-msft 

Even though the pareto closed at 100% the cumulative is not summing correctly. This is because when the count column repeats a value, the cumulative wont sum it:

Where:

Rank Kore requirement = 
RANKX (
CALCULATETABLE ( DISTINCT ( GAO[kore_requirement_code] ), ALLSELECTED ( gao ) ),
CALCULATE(COUNT(GAO[kore_requirement_code])),
,
DESC,

Skip
)

Cumulative kore requirement = SUMX(
TOPN([Rank Kore requirement],
ALL(GAO[kore_requirement_code]),[kore requirement dax],DESC),[kore requirement dax])

kore requirement dax = CALCULATE(COUNT(GAO[kore_requirement_code]))

How can the cumulative keep summing? it should look like column 😧

Thanks.

[KarlaBMS93]

Hello, did you manage to solve your problem, is that I have the same problem with my count column?

[Greg_Deckler]

Difficult to say, sample data as text. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[o59393]

Apologies @Greg_Deckler 

Here you have the pbix https://1drv.ms/u/s!ApgeWwGTKtFdhkdsKksoIp_tIGCf?e=dJLGT4

For some reason the rank is not displaying any other value rathet than 1. 

Thank you for the help.