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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rampbi_6
New Member

Pareto with SUMX Function resulting in cumulative sum with duplicate values...Please help

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 : 

 

DefectCount of Defect OccurenceCumulative SumIdeal Sum (what it should be)
A666
B41410
C41414

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!!

rampbi_6_0-1679658458951.png

 

 

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

 

 

1 REPLY 1
amitchandak
Super User
Super User

@rampbi_6, Try window function

Power BI Window: Pareto Analysis Again, 80% of sales, Order by Measure when REL position is used: https://youtu.be/GpoITi_tRIw

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.