Pareto with SUMX Function resulting in cumulative sum with duplicate values

[Anonymous]

Hello all, 

I am brand new to PowerBI and I'm making a pareto chart to show defect count. I'm having an issue whenever there are two or more defects with the same amount of occurrences.  For example, when I use the summarize function to create a table of counts and then use SUMX to get the sum, the below table would appear : 

Defect	Count of Defect Occurence	Cumulative Sum	Ideal Sum (what it should be)
A	6	6	6
B	4	14	10
C	4	14	14

This creates a flat line on the pareto wherever there are duplicate values even though it should be curved (I have pasted an example of my code and pasted the image of what I am getting below).

I have tried numerous solutions to this including trying to use RANKX and conditional statements but I can't seem to get anything to work.  I have spent so many more hours on this than I originally planned, please any help would be super appreciated!!

Defect Cumulative Percent = 
VAR deftotal = CALCULATE(COUNT(Sheet1[Defect]),ALLSELECTED(Sheet1[Defect]))
VAR subcount = COUNT(Sheet1[Defect])
VAR Counttable = 
FILTER(
    SUMMARIZE(
        ALLSELECTED(Sheet1[Defect]),
        Sheet1[Defect],
        "Defect Count", Count(Sheet1[Defect])),
        Sheet1[Defect] <> Blank()
)
VAR Cumulativesum = 
SUMX(
    FILTER(Counttable, [Defect Count] >= SUBCOUNT),
    [Defect Count]
)
RETURN
Cumulativesum/deftotal

 Thanks,
Sam

[ryan_mayu · Accepted solution]

@Anonymous 

you can rank defect to solve this problem. I did the similar test before. pls see the attachment below

[Anonymous]

Thank you! I was finally able to get this to work!  I only had to make an adjustment to your code where you created the secondary rank based off of the ID column.  Due to the name of my defects being varied the rank was returning incorrect values, instead I had it refer to a numerical ID column (similar to an index column) that was already in my table and used that as the secondary rank reference.

Finished product: 

[rampbi_6]

Hi @Anonymous 

can you tell me more like what all changes have u done and the modified measure.

Quick response would be appreciated.

I have also written the same measure but stucked at duplicate values getting same cumulative percentage

Defect Cumulative Percent = 
VAR deftotal = CALCULATE(COUNT(Sheet1[Defect]),ALLSELECTED(Sheet1[Defect]))
VAR subcount = COUNT(Sheet1[Defect])
VAR Counttable = 
FILTER(
    SUMMARIZE(
        ALLSELECTED(Sheet1[Defect]),
        Sheet1[Defect],
        "Defect Count", Count(Sheet1[Defect])),
        Sheet1[Defect] <> Blank()
)
VAR Cumulativesum = 
SUMX(
    FILTER(Counttable, [Defect Count] >= SUBCOUNT),
    [Defect Count]
)
RETURN
Cumulativesum/deftotal

[ryan_mayu]

you are welcome