Running totals column in power pivot data model

You can create a MEASURE to evaluate a cumulative total in [Index]:

Please Login or Register  to view this content.

You were nearly there - you just needed to remove the row / evaluation context before the comparative transition.

Also - using a variable is generally simpler then using the EARLIER function.

You don't need SUMX - this is an iterator, and in this case, you're doing a simple aggregation of a single column. You do need a context transition, which is why we use the CALCULATE function.

Why are you trying to add this as a Calculated Column? If you do this, you lose the ability to perform your cumulative total against slices of your data, and it makes aggregation harder, not easier. Unless you're planning to group by cumulative total values, then it makes sense to add this as a MEASURE, not as a CALCULATED COLUMN.

In your sample workbook, the measure would read:

Please Login or Register  to view this content.

(My apologies - the version of Excel I am using in the office today is without PowerPivot, so I can't quite see what you're trying in your calculated columns. But my advice to use a measure stands.)

Variables are really useful for improving performance (calculate once, use several times), and for making context transitions easier (in this case, the variable is calculated before the context transition, so the EARLIER transition function is not needed). They also help keep your DAX code simpler and easier to trace.

"I prefer to see what I'm getting in a calculated column" - ah, the typical refrain of the Excel user! There are several reasons why calculated columns are not always the best option. A brief example: in this case, imagine your data has an additional column - let's call it Colour. Now, if you use a calculated column for your cumulative total, you can no longer split that cumulative total by colour value. Creating a measure allows you to evaluate the formula within the context of the report - so if you have grouped your report by Colour, each cumulative colour value will be calculated appropriately and correctly, without needing to set up multiple calculated columns in advance. There are also performance benefits - calculated columns need to be evaluated for every row of your table, every time it is refreshed. Measures only calculate as needed within the report. Measures are immensely powerful.

Here's someone much smarter than me, explaining it much better: https://exceleratorbi.com.au/calcula...-measures-dax/

Finally, "Context". There are three types on context to understand in DAX (Row context, Filter context and Evaluation context), and it's worth taking the time to learn the theory, and understand context transition. Here's a reasonable starting point: https://adatis.co.uk/Understanding-D...ation-Context/

Last bit of advice from me: Read sqlbi.com, and order their books. It's gold.