Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 Anonymous ,

     

    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
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    whats your desired output?

    Its unclear as you didn't provide an excel example

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
     

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    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
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    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.