Forum Discussion

jjbates's avatar
jjbates
Regular Visitor
9 years ago
Solved

SUMIF in Query Editor on Power BI Desktop?

I am trying to do a simple sumif in the Query Editor of the PowerBI desktop. I cannot edit the source data, so I need a calculated column as a new column that simply sums one column if it matches the...
Tips and Tricks
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi jjbates,

     

    You can open Query Editor, use Group By feature:

     

     

    The backend M formula is below:

     

    = Table.Group(dbo_DimSalesTerritory, {"SalesTerritoryGroup"}, {{"Total", each List.Sum([SalesTerritoryKey]), type number}})

     

    Best Regards,
    Qiuyun Yu

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

For example the sum of all values where Group = "A":

= List.Sum(Table.SelectRows(PreviousStep, each [Group]="A")[Value])

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Marcel,

     

    I am trying your formula but I cannot get it to work.


    I am doing something a little more complicated:

     

    = Table.Group(#"Removed Errors", {"order no."}, {{"Adj Gross Value", List.Sum(Table.SelectRows(PreviousStep, each Text.Start([ReferenceID], 3) = "ADJ") [Gross Value]), type number}}

     

    As you can see I want to match using Text.Start but I cannot get it to work. This is my error:

     

    Expression.Error: The name 'PreviousStep' wasn't recognized. Make sure it's spelled correctly.

     

    If you can help thanks in advance.