Rob Collie explains the power of SUMX() in DAX:
Have you ever written an array formula in Excel? (Don’t worry, most people haven’t). Have you ever written a FOR loop in a programming language? (Again, don’t worry, there’s another question coming). Have you every repeated something over and over again, slowly building up to a final result?
That’s what SUMX() does. It loops through a list, performs a calc at each step, and then adds up the results of each step. That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.
Read on for a few examples.