Slow cumulative sum - ABC analysis

[FelipeDBA]

Hi, I'm struggling  with a kind of abc analysis. I have a dimesion with around 200 thousand customer and a fact table with around 20M rows. What I need is count the number of customers that represents 80% of my sells.

I've done the measure it is super slow and I have no idea of what to do anymore. Long story short, i've built a temp table with all the column that I need, including the Percentage of each customer in the total and it doest take more then 2 seconds to run, but when I add the cumulative sum, it goes beyond 20 seconds.. Here is my final code:

var v4 = 
ADDCOLUMNS(
 v3
,"mysum"
SUMX(
FILTER(v3, EARLIER([% Percentage on total]) <=[%Percentage on total])
   ,[%Percentage on total]
)


return
SUMX(
FILTER(v4,[mysum] <=80),1
)

Thank you in advanced.

[amitchandak]

@FelipeDBA , see if this can make it faster

https://finance-bi.com/power-bi-pareto-analysis/

[parry2k · Accepted solution]

@FelipeDBA performance of the measure is dependent on many factors, like your data model, the relationship of the tables etc. It is not easy and straight forward answers, I have worked with tables over 100m rows and with DAX studio, I was able to tweak the measures to perform faster, in nutshell, it is not easy without looking at all the details, model and relationship and what you are trying to do. I know it is not a solution to your question, but there is no other shortcut.