Replicate SUMIF in Power Query

[emredici]

Hi all,

I have the below table in Power BI Power query.

I wanted to simplify the subject and paste the Excel copy of it instead.

The columns with blue titles are my available data. I want to generate the Green column in Power query.

It's so simple in excel. As shown below. 

How can I do the same operation in Power BI? I would like to return each sumif figure on each row.

Ultimately, my goal is to add a new column and generate a calculated copy of the highligted column in PBI power query.

Thank you.

[ronrsnfld · Accepted solution]

• Group by Activity ID
• Aggregate by
  • All Rows
  • Sum of Working Day Count
• Expand the "All Rows" aggregation

[emredici]

Hi @ronrsnfld,

Thanks for the response.

I grouped the data by the Activity ID.

I aggregated all with sum of working day count. However, I couldn't see anything regarding "all rows"

The end result is this. I can't aggregate or select All Rows.

[ronrsnfld · Accepted solution]

It does not appear that you are in the Query Editor (Power Query).

Select `Home=>Transform`

Then you will find the GroupBy option and, when the dialog box opens, select Advanced to be able to add multiple aggregations/operations

[emredici]

Thank you! It is resolved. You were extremely fast 😄
This forum is lucky to have someone like you.

[ronrsnfld · Accepted solution]

• Group by Activity ID
• Aggregate by
  • All Rows
  • Sum of Working Day Count
• Expand the "All Rows" aggregation