How to calculate running total column break down by product in Power query editor

[VamshiGoud]

I'm getting Month wise data . now i want to calculate cumulative/running total  break down by product .This  running total needs to be done in query editor. final Out put column is there for refrence. Can anyone share the logic .

Product	Date(mm/dd/yyyy)	Amount	Out put
A	01-01-2025	10	10
A	02-01-2025	20	30
A	03-01-2025	10	40
A	04-01-2025	30	70
A	05-01-2025	40	110
B	01-01-2025	20	20
B	02-01-2025	30	50
B	03-01-2025	20	70
B	04-01-2025	10	80
B	05-01-2025	50	130

[v-karpurapud]

Hi @VamshiGoud 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @VamshiGoud 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @VamshiGoud 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @Shahid12523 , @MohamedFowzan1 and @anmolmalviya05 for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.

Thank you.

[anmolmalviya05]

Hi @VamshiGoud, Hope you are doing good!
You can try the below approach to achieve the desired result:

Steps in Power Query Editor

Sort your table )Go to Home > Sort Ascending by Product, then Date)

Group by Product (Go to Home > Group By.)

Add Index Column per Group (Inside the grouped table for each product, add an Index Column (starting from 1).)

Add a Custom Column with this formula: List.Sum(List.FirstN([Amount], [Index]))

Expand back the grouped tables (Click the expand icon to bring everything back into a single flat table.)

[MohamedFowzan1]

Hi @VamshiGoud 

I was able to make this work:
Here is the MCode for your reference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIwMjUyDH0EApVgcqboQkboQkboxDvQmSuDGSuCmSuAlE3AndXiMkcSNMc5zQ7UVWb4LpHid0e02B4rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Date(mm/dd/yyyy)" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date(mm/dd/yyyy)", type date}, {"Amount", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Product", Order.Ascending}, {"Date(mm/dd/yyyy)", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product"}, {{"AllData", each _, type table [Product=nullable text, #"Date(mm/dd/yyyy)"=nullable date, Amount=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
    AddIndex = Table.AddIndexColumn([AllData], "Index", 1, 1, Int64.Type),
    AddRunningTotal = Table.AddColumn(AddIndex, "RunningTotal", each List.Sum(List.FirstN(AddIndex[Amount], [Index])))
in
    AddRunningTotal),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllData"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Product", "Date(mm/dd/yyyy)", "Amount", "Index", "RunningTotal"}, {"Product.1", "Date(mm/dd/yyyy)", "Amount", "Index", "RunningTotal"})
in
    #"Expanded Custom"

 
First Sort the rows:

Then Group the rows:

Then add a custom column:

Then remove the AllData column:

Finally Expand the columns:

[Shahid12523]

In Power Query, to get a running total by product:

Sort by Product and Date.

Group By Product → operation: All Rows.

Inside each group, add a custom column using:

Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)

Then create running total:

Table.AddColumn(_, "RunningTotal", each List.Sum(List.FirstN(_[Amount],[Index])))

Expand the table back.

This gives a cumulative total per product like your example.