Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
alvaroiark
New Member

Calculate column with digited value

Hi All,


I need to do the same calculation as in the image below, with custom column and m language, however the first value is manually inputed, that is, it is a fixed value, the sequence takes the prior value and adds and decreases the cells.

 

screen_capture.png

 

Best Regards,

Alvaro

1 ACCEPTED SOLUTION
collinsg
Super User
Super User

Good day Alvaro,

This problem is a variation on the theme of calculating a running total. Nick de Groot has a comprehensive article on running totals here. My solution uses a simple but slow approach to running total but if performance is a concern you could use one of the faster methods described by Nick.

 

My data looked like this – the balance column contained nulls except for the first row.

collinsg_0-1685516563186.png

 

My steps were,

  1. Replace the nulls in the Balance column with zero.
  2. Add an index column.
  3. Add a column called “i1+i2-o1-o2” which calculates Input1 + Input2 – Output1 – Output2.
  4. Add a column called “Running Total” using the List.Sum and List.FirstN functions.
  5. Remove unneeded column.
  6. Rename “Running Total” as “Balance”.

There will be more elegant ways to do this without adding then removing columns but this step by step approach makes the route to the solution simple to follow.

 

This is my output

collinsg_2-1685516755709.png

 

My M code is here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Input1", Int64.Type}, {"Input2", Int64.Type}, {"Output1", Int64.Type}, {"Output2", Int64.Type}, {"Balance", Int64.Type}}),

    #"Replaced nulls in Balance" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Balance"}),
    #"Add Index" = Table.AddIndexColumn(#"Replaced nulls in Balance", "Index", 1, 1, Int64.Type),
    #"Added i1+i2-o1-o2" = Table.AddColumn(#"Add Index", "i1+i2-o1-o2", each [Balance] + [Input1] + [Input2] - [Output1] - [Output2], Int64.Type),
    #"Add running total" = Table.AddColumn(
#"Added i1+i2-o1-o2",
"Running Total",
each List.Sum( List.FirstN( #"Added i1+i2-o1-o2"[#"i1+i2-o1-o2"], [Index] ) ),
Int64.Type ),
    #"Remove other columns" = Table.SelectColumns(#"Add running total",{"Date", "Input1", "Input2", "Output1", "Output2", "Running Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Remove other columns",{{"Running Total", "Balance"}})
in
    #"Renamed Columns"

Hope this helps

View solution in original post

2 REPLIES 2
alvaroiark
New Member

Hi @collinsg ,

 

It worked perfectly!

Thx so much!

 

Best Regards,

Alvaro

collinsg
Super User
Super User

Good day Alvaro,

This problem is a variation on the theme of calculating a running total. Nick de Groot has a comprehensive article on running totals here. My solution uses a simple but slow approach to running total but if performance is a concern you could use one of the faster methods described by Nick.

 

My data looked like this – the balance column contained nulls except for the first row.

collinsg_0-1685516563186.png

 

My steps were,

  1. Replace the nulls in the Balance column with zero.
  2. Add an index column.
  3. Add a column called “i1+i2-o1-o2” which calculates Input1 + Input2 – Output1 – Output2.
  4. Add a column called “Running Total” using the List.Sum and List.FirstN functions.
  5. Remove unneeded column.
  6. Rename “Running Total” as “Balance”.

There will be more elegant ways to do this without adding then removing columns but this step by step approach makes the route to the solution simple to follow.

 

This is my output

collinsg_2-1685516755709.png

 

My M code is here.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Input1", Int64.Type}, {"Input2", Int64.Type}, {"Output1", Int64.Type}, {"Output2", Int64.Type}, {"Balance", Int64.Type}}),

    #"Replaced nulls in Balance" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Balance"}),
    #"Add Index" = Table.AddIndexColumn(#"Replaced nulls in Balance", "Index", 1, 1, Int64.Type),
    #"Added i1+i2-o1-o2" = Table.AddColumn(#"Add Index", "i1+i2-o1-o2", each [Balance] + [Input1] + [Input2] - [Output1] - [Output2], Int64.Type),
    #"Add running total" = Table.AddColumn(
#"Added i1+i2-o1-o2",
"Running Total",
each List.Sum( List.FirstN( #"Added i1+i2-o1-o2"[#"i1+i2-o1-o2"], [Index] ) ),
Int64.Type ),
    #"Remove other columns" = Table.SelectColumns(#"Add running total",{"Date", "Input1", "Input2", "Output1", "Output2", "Running Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Remove other columns",{{"Running Total", "Balance"}})
in
    #"Renamed Columns"

Hope this helps

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors