When I was working recently with a client, helping her remotely – I asked her to calculate the sum for sales amount in the table. She responded whether she should use SUM, SUMX or CALCULATE?
Simple question, but not a simple answer. Or I can give you the classic lawyer response – “It depends!” Let’s review.
SUM: Simple Unmitigated Magic
The good news is that a simple SUM, would work in majority of the cases. A simple sum in the hands of Power Pivot is a powerful tool. With the magic of relationships a simple SUM can show you tricks you could never have imagined in Excel.
Power Pivot relationships mean, that you define your measures once and use them everywhere.
Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use any slicers and the measures would still work.
Only when you see the results and go…”Uh…that is not exactly what I want”, should you explore other options.