cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

Best Regards,

Alvaro

1 ACCEPTED SOLUTION
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.

My steps were,

1. Replace the nulls in the Balance column with zero.
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

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

2 REPLIES 2
New Member

Hi @collinsg ,

It worked perfectly!

Thx so much!

Best Regards,

Alvaro

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.

My steps were,

1. Replace the nulls in the Balance column with zero.
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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors