Forum Discussion

FelipeDBA's avatar
FelipeDBA
Frequent Visitor
6 years ago
Solved

Slow cumulative sum - ABC analysis

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.

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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.

2 Replies

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      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.