Running Total in PowerPivot

[MI_2016]

Hi There

I am trying to recreate a formula I have been using in an Excel table within a new Power Pivot version of the table to create the same running total in the 'Cumulative Paid' column.

Currently the formula I have in my normal Excel table is: =SUMIF($S$2:S2,S2,$T$2:T2) and works exactly how I want it to when copied down column U.

The total runs from top to bottom based upon the Key value in column S.

I have been trying with the EARLIER function in Power Pivot to achieve this but not quite managed to get it returning the results in the same manner as the SUMIF formula above.

Any suggestions?

Thanks in advance.




[TABLE="width: 603"]
<tbody>[TR]
[TD]COLUMN S[/TD]
[TD]COLUMN T[/TD]
[TD]COLUMN U[/TD]
[/TR]
[TR]
[TD]Key[/TD]
[TD]Paid [/TD]
[TD]Cumulative Paid[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER3_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER3_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]147.8230769[/TD]
[TD="align: right"]147.8230769[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]64.93846154[/TD]
[TD="align: right"]212.7615385[/TD]
[/TR]
[TR]
[TD]CUSTOMER3_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]538.7[/TD]
[TD="align: right"]538.7[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]662559.3618[/TD]
[TD="align: right"]662772.1233[/TD]
[/TR]
[TR]
[TD]CUSTOMER3_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER4_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER3_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER5_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER4_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER2_2011[/TD]
[TD="align: right"]13267.52692[/TD]
[TD="align: right"]13806.22692[/TD]
[/TR]
[TR]
[TD]CUSTOMER6_2011[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CUSTOMER1_2011[/TD]
[TD="align: right"]39781.85173[/TD]
[TD="align: right"]702553.975[/TD]
[/TR]
</tbody>[/TABLE]

 

[gazpage]

DAX generally doesn't guarantee any order to what it returns. I think to do a calculation like this you need to add an index column. See my M code for how I added one to your example data.

let
    Source = Web.Page(Web.Contents("https://www.mrexcel.com/forum/power-bi/1057527-running-total-powerpivot.html")),
    Data0 = Source{0}[Data],
    #"Removed Top Rows" = Table.Skip(Data0,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Key", type text}, {"Paid", type number}, {"Cumulative Paid", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Key", "Paid", "Cumulative Paid"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Cumulative Paid"})
in
    #"Removed Columns"

Then I wrote a measure:

Paid running total in Index = 
CALCULATE(
 SUM('MrExcel'[Paid]),
 FILTER(
  ALL('MrExcel'[Index]),
  MrExcel[Index] <= MAX('MrExcel'[Index])
 )
)

I was then able to create your running total column exactly.

 

[MI_2016]

Great, thanks for taking the time help & reply