Forum Discussion

larabraghetti's avatar
larabraghetti
Icon for Helper II rankHelper II
10 years ago
Solved

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 portfolio, the sum of the market value of the portfolio.

So for all rows with portfolio1 I will have always the same number (the sum of market value of this portfolio).

I don't know how to do this in Power BI. 
Please, could you help me?
Thank you!
Lara

Need Help
  • leonardmurphy's avatar
    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.

     

     

2 Replies

  • leonardmurphy's avatar
    leonardmurphy
    Icon for Skilled Sharer rankSkilled 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.