SUMX() is one of my favorite functions, but it can be tricky to understand how it works some times. Hopefully, this video can help you understand this function a little better.
The points I make in the video:
- Normally, in DAX we cannot do math using column names for additions and multiplications, however, when we are trying to add a new column to a table in a model, we can work with columns the same way we can in excel
- The same formula that works in a create column scenario often will not work when you try to write the same DAX to create a measure
- Adding new columns simplifies things for those of us who are not DAX experts, but also causes our models to get larger and in some cases harder to maintain, so we should try to use measures as opposed to calculated columns as much as possible
- SUMX() is one of a few DAX functions that enjoys the same benefits as a calculated column as it allows us to use column names in our arithmetic as it executes the DAX expression in its second argument one row at a time, which reduces the column name used in the expression to a single cell.