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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.