Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Pareto Chart

Hi all,

 

I am working on a pareto chart but am facing an error whereby cumulative summing doesn't work when there are categories with the same count as seen below

  Cat A w/ sub-categories with same count                                   Cat B w/ sub-categories of unique counts

Below is the code used for the line measure

 

Pareto Measure = 
VAR TotalCount = CALCULATE(
        DISTINCTCOUNT('Aircraft Records'[Investigation  Report No. (INV)]),
        FILTER(
            ALLSELECTED('Aircraft Records'),
            'Aircraft Records'[Investigation  Report No. (INV)] <> BLANK()
        )
    )

VAR currentCount = 
    CALCULATE(
        DISTINCTCOUNT('Aircraft Records'[Investigation  Report No. (INV)]),
        FILTER(
            'Aircraft Records',
            'Aircraft Records'[Investigation  Report No. (INV)]<> BLANK()
        )
    )


VAR summarisedtable =
    SUMMARIZE(
        FILTER(
            ALLSELECTED('Aircraft Records'),
            'Aircraft Records'[Investigation  Report No. (INV)] <> BLANK()
        ),
        'Aircraft Records'[Sub-Root Cause],
        "CountInv",
        DISTINCTCOUNT('Aircraft Records'[Investigation  Report No. (INV)])
    )

VAR cumulative = sumx(filter(summarisedtable, [CountInv] >= currentCount),[CountInv])

return cumulative/TotalCount

 

 

Any help would be very much appreciated!!

2 Replies

  • Anonymous , Please use Window function for that, yiu can easily break ties using order by

    Cumm = CALCULATE( DISTINCTCOUNT('Aircraft Records'[Investigation Report No. (INV)]), Window(1,ABS,0,REL, ALLselected('Aircraft Records'[category]),ORDERBY( DISTINCTCOUNT('Aircraft Records'[Investigation Report No. (INV)]),desc,'Aircraft Records'[category], asc )))

     

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

     

    https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

     

    Or try with Range Visual level funtion

    🚀 Master Visual Calculations in Power BI- February 2024 Update RUNNINGSUM, RANGE, MOVINGAVERAGE, COLLAPSE, COLLAPSEALL, EXPAND, EXPANDALL, FIRST, LAST, PREVIOUS, and NEXT
    https://www.youtube.com/watch?v=bKD9T0EWgQo&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Amit,

       

      I've tried the formula that you have provided but the values are not correct.