Forum Discussion

sunil3M's avatar
sunil3M
New Member
6 years ago
Solved

SUMIF function

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 QTYOrder Qty
IA110123493460460
IA11012349350005460
IA110123493500010460
IA110123493150011960
IA110123493200013960
IA11012349350014460
IA110123493160016060
IA110123493100017060
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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.

     

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    sunil3M 

    whats your desired output?

    Its unclear as you didn't provide an excel example

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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]))
)
  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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.