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.

COLUMN S	COLUMN T	COLUMN U
Key	Paid	Cumulative Paid
CUSTOMER1_2011	0	0
CUSTOMER1_2011	0	0
CUSTOMER1_2011	0	0
CUSTOMER1_2011	0	0
CUSTOMER2_2011	0	0
CUSTOMER1_2011	0	0
CUSTOMER1_2011	0	0
CUSTOMER2_2011	0	0
CUSTOMER2_2011	0	0
CUSTOMER1_2011	0	0
CUSTOMER3_2011	0	0
CUSTOMER3_2011	0	0
CUSTOMER2_2011	0	0
CUSTOMER1_2011	147.8230769	147.8230769
CUSTOMER1_2011	64.93846154	212.7615385
CUSTOMER3_2011	0	0
CUSTOMER2_2011	0	0
CUSTOMER2_2011	538.7	538.7
CUSTOMER1_2011	662559.3618	662772.1233
CUSTOMER3_2011	0	0
CUSTOMER4_2011	0	0
CUSTOMER3_2011	0	0
CUSTOMER5_2011	0	0
CUSTOMER4_2011	0	0
CUSTOMER2_2011	13267.52692	13806.22692
CUSTOMER6_2011	0	0
CUSTOMER1_2011	39781.85173	702553.975

<tbody>
</tbody>

 

[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