Forum Discussion

nironixon's avatar
nironixon
Frequent Visitor
7 years ago

Running Total on the given dataset?

Hi there, Can anyone help me or point me in the right direction how to calculate the running total column on the following example dataset in powerbi;   ACCOUNTS Table: ID | CREATEDDATE | DELETE...
Need Help
PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

nironixon Please try this in "Power Query"

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AUiIwNDCyBHKVYnWskIq6gxSNQIXdQEJGqMJGpgAueA5E1RhUC6YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, CreatedDate = _t, DeletedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"CreatedDate", type date}, {"DeletedDate", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Created", each if [CreatedDate] <> null then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Deleted", each if [DeletedDate] <> null then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column1", {"CreatedDate"}, {{"Created", each List.Sum([Created]), type number}, {"Deleted", each List.Sum([Deleted]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Total", each [Created]-[Deleted]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1),
    #"Added Running Total" = Table.AddColumn(#"Added Index", "RunningTotal", each List.Sum(List.FirstN(#"Added Index"[Total],[Index]+1))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Running Total",{"Total", "Index"})
    
in
    #"Removed Columns"

Here is the expected output !!

 

nironixon's avatar
nironixon
Frequent Visitor
7 years ago

Hi there and thanks for your efforts and it def. works on a small dataset!

 

The dataset im working on have approx 1.300.000 rows and the query times out. 

I think it is the #"Added Running Total" statement that sums up rows up to the current index that performs badly;

 

#"Added Running Total" = Table.AddColumn(#"Added Index", "RunningTotal", each List.Sum(List.FirstN(#"Added Index"[Total],[Index]+1))),

 

Any idea how we can fix this, im totaly blank!

Niclas

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi nironixon,

     

    I'd like to suggest you do running total on data view side with dax calculate column, it has better performance than direct invoke all list of data to sum up.

    Running Total=
CALCULATE (
    SUM ( Table[Created] ),
    FILTER ( ALL ( Table ), [Date] <= EARLIER ( Table[Date] ) )
)

     

    Regards,

    Xiaoxin Sheng

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    This can be done with a DAX formula.  In the visual, you would drag Account to the row labels and then write a mesure.  Would you be interested in that solution?