DAX – SUM, SUMX or CALCULATE()…Choices, choices!

By Avichal Singh (Avi) 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. “Define Once, Use Everywhere” Your measures conform to the shape of your pivot, so you can drag and drop any fields from your model, use … Continue reading DAX – SUM, SUMX or CALCULATE()…Choices, choices!