Help... SUM.IF in Power BI?

Dear all, I' like to build a new column formula like this one: =SUM.IF(COLUMN[Portfolios];COLUMN[Portfolios]=ROWn;MARKET VALUE) I try to explain: I'd like to have for each line with the same...

Need Help

leonardmurphy
10 years ago
This is certainly possible in Power BI. Try:

Portfolio Market Value = CALCULATE(SUM([Market Value]), ALLEXCEPT(TableName, TableName[Portfolio]))

(Assuming your table is called TableName and it has at least 2 columns: Portfolio and Market Value.)

The SUM([Market Value]) in a calculated column will calculate just the sum of the row it is in. By wrapping it in a CALCULATE with an ALLEXCEPT clause, the ALLEXCEPT says "remove all filters on this table, except the filter on Portfolio". The entire formula is saying "I want to sum the Market Value for every row in my table where the portfolio is the same as the portfolio in the current row.

leonardmurphy

Skilled Sharer

10 years ago

This is certainly possible in Power BI. Try:

Portfolio Market Value = CALCULATE(SUM([Market Value]), ALLEXCEPT(TableName, TableName[Portfolio]))

(Assuming your table is called TableName and it has at least 2 columns: Portfolio and Market Value.)

The SUM([Market Value]) in a calculated column will calculate just the sum of the row it is in. By wrapping it in a CALCULATE with an ALLEXCEPT clause, the ALLEXCEPT says "remove all filters on this table, except the filter on Portfolio". The entire formula is saying "I want to sum the Market Value for every row in my table where the portfolio is the same as the portfolio in the current row.

larabraghetti

Helper II

10 years ago

Thanks!
It works. Well done.

Lara

Forum Discussion

Help... SUM.IF in Power BI?