Need help with the formula to calculate the actual balance amount of the Dealer against the invoices

[Chandrakant]

Hi,
Need help with the formula to calculate the actual balance amount of the Dealer against the invoices.

In have calculated the same but my formula works only for 1 Dealer, when there are multiple Dealers it goes wrong.

My calculation is in Result ( H ) Column and what i want is in Expected Result ( I ) Column.

any help would be greatly appreciated.

Have attache the sample file for your reference.

 

[AlanSidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Dealer Name"}, {{"DEBITS", each List.Sum([Debit]), type nullable number}, {"CREDITS", each List.Sum([Credit]), type nullable number}, {"OPENING", each List.Sum([Opening Balance]), type nullable number}}),
    #"Inserted Addition" = Table.AddColumn(#"Grouped Rows", "DEBIT", each [OPENING] + [DEBITS], type number),
    #"Inserted Subtraction" = Table.AddColumn(#"Inserted Addition", "Totals", each [DEBIT] - [CREDITS], type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Subtraction",{"Dealer Name", "DEBIT", "CREDITS", "Totals"})
in
    #"Removed Other Columns"