Running Total in PowerPivot

MI_2016

New Member
Joined
Dec 22, 2016
Messages
4
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 SCOLUMN TCOLUMN U
KeyPaid Cumulative Paid
CUSTOMER1_201100
CUSTOMER1_201100
CUSTOMER1_201100
CUSTOMER1_201100
CUSTOMER2_201100
CUSTOMER1_201100
CUSTOMER1_201100
CUSTOMER2_201100
CUSTOMER2_201100
CUSTOMER1_201100
CUSTOMER3_201100
CUSTOMER3_201100
CUSTOMER2_201100
CUSTOMER1_2011147.8230769147.8230769
CUSTOMER1_201164.93846154212.7615385
CUSTOMER3_201100
CUSTOMER2_201100
CUSTOMER2_2011538.7538.7
CUSTOMER1_2011662559.3618662772.1233
CUSTOMER3_201100
CUSTOMER4_201100
CUSTOMER3_201100
CUSTOMER5_201100
CUSTOMER4_201100
CUSTOMER2_201113267.5269213806.22692
CUSTOMER6_201100
CUSTOMER1_201139781.85173702553.975

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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.

Code:
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:

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,230
Members
409,857
Latest member
KailuaTown
Top