SUMIF function

[sunil3M]

this is the formula i use in excel SUMIF($P$2:P17,P17,$AF$2:AF17)

want to replicae the same in power bi

can some one share me easy coding for the same

below is the data from excel and Order qty column is the column were i use this formula.

based on the SKU need to sum the value 

SKU #	BACK QTY	Order Qty
IA110123493	460	460
IA110123493	5000	5460
IA110123493	5000	10460
IA110123493	1500	11960
IA110123493	2000	13960
IA110123493	500	14460
IA110123493	1600	16060
IA110123493	1000	17060

[v-eachen-msft · Accepted solution]

Hi @sunil3M ,

Power query could also do it.

You could create an index column and a "RunningTotal" function in query editor.

= ( tbl as table,  sumcolumn as text, rowindex as number) =>
let
    #"Removed Columns" = Table.RemoveColumns(tbl,{"SKU #"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Index] <= rowindex),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
    RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
in
    RunningTotal

Then use it to add a new column

=
Table.AddColumn(#"Removed Columns", "Custom", each TotalRunning(#"Removed Columns","BACK QTY",[Index]))

You could get more details from this link:

https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/

Here is my test file for your reference.

[az38]

@sunil3M 

first, create an Index column in Power Query then create a calculated column

Order Qty = 
CALCULATE(
SUM(Table[SKU #]), 
FILTER(ALL(Table), Table[SKU #] = EARLIER(Table[SKU #]) && Table[Index] <= EARLIER(Table[Index]))
)

[sunil3M]

[az38]

@sunil3M 

whats your desired output?

Its unclear as you didn't provide an excel example