The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!!
Solved! Go to Solution.
@qnnn8 , 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
@qnnn8 , 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
Hi Amit,
I've tried the formula that you have provided but the values are not correct.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
82 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |