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
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
@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
User | Count |
---|---|
159 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
157 | |
137 | |
131 | |
81 | |
61 |