cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothschild Member
Member

SUMIFs/CALCULATE not doing what I need it to do

Power BI sample here (page 4) https://www.dropbox.com/s/rbjoqspdjdmtquq/Power%20BI%20Forum%20Sample.pbix?dl=0

Excel Data here:  https://www.dropbox.com/s/i95ssp7bjxcglzt/Portfolio%20Tool%20-%20Standardized%20Multi%20Asset%20Mode...

 

As shown in column BY of the Excel Data, I have used the following excel formula: '=SUMIFS([Cumulative IRR - Upside],[Asset ID],[@[Asset ID]],[Count],[Project Term (mos)]+1) to generate flattened data of an output on an [Asset ID] by [Asset ID] level

 

My attempt to create the same in Power BI is the following programming:

 

CALCULATE(
    SUM('_Bronn Portfolio Analysis Table'[Cumulative IRR - Upside]),
     ALLSELECTED('_Bronn Portfolio Analysis Table'[Asset ID]),
FIlter('_Bronn Portfolio Analysis Table','_Bronn Portfolio Analysis Table'[Count]='_Bronn Portfolio Analysis Table'[Project Term (mos)]+1)
)

 

The problem I have with this is that some [Project]s have one [Asset ID] and some have multiple.  As an example, if you select "Alpha" in the BI slicer, the output from the above Measure is 62.4%.  Alternatively, if you select "Charlie" in the BI slicer, the output is 21.1%.  The only difference between these is that there are 3 [Asset ID]s in "Alpha" and only one in "Charlie".  Because there may be different purchase prices on an [Asset ID] by [Asset ID] level, I don't think the right answer is dividing by number of [Assets ID]s., so essentially, I'm trying to get a weighted average of the output, weighted in this case by the [Equity Contribution] column.

 

Help please

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUMIFs/CALCULATE not doing what I need it to do

@mrothschild

 

To solve the immediate problem with the data as it is currently structured, try this measure:

 

IRR Forecast Upside Weighted Average =
CALCULATE (
    DIVIDE (
        SUMX (
            '_Bronn Portfolio Analysis Table',
            '_Bronn Portfolio Analysis Table'[Cumulative IRR - Upside] * '_Bronn Portfolio Analysis Table'[Equity Contribution]
        ),
        SUM ( '_Bronn Portfolio Analysis Table'[Equity Contribution] )
    ),
    ALLSELECTED ( '_Bronn Portfolio Analysis Table'[Asset ID] ),
    FILTER (
        SUMMARIZE (
            '_Bronn Portfolio Analysis Table',
            '_Bronn Portfolio Analysis Table'[Count],
            '_Bronn Portfolio Analysis Table'[Project Term (mos)]
        ),
        '_Bronn Portfolio Analysis Table'[Count] = '_Bronn Portfolio Analysis Table'[Project Term (mos)] + 1
    )
)

 

  • The average of Cumulative IRR - Upside weighted by Equity Contribution is in green.
    • This expression makes sense as long as the final FILTER results in one row per Asset ID (appears to be the case)
  • I also rewrote the FILTER as FILTER ( SUMMARIZE (...) ), in order to apply a filter just on the required columns.
  • I'm not sure the ALLSELECTED is required, but it depends how you are using this measure so left it there.

 

As a side note, I would consider restructuring the data model so that you have an Asset table (one row per asset) related to the '_Bronn Portfolio Analysis Table'. This would avoid repeating common values across every row of a given Asset ID, and may simplify some of the DAX.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: SUMIFs/CALCULATE not doing what I need it to do

@mrothschild

 

To solve the immediate problem with the data as it is currently structured, try this measure:

 

IRR Forecast Upside Weighted Average =
CALCULATE (
    DIVIDE (
        SUMX (
            '_Bronn Portfolio Analysis Table',
            '_Bronn Portfolio Analysis Table'[Cumulative IRR - Upside] * '_Bronn Portfolio Analysis Table'[Equity Contribution]
        ),
        SUM ( '_Bronn Portfolio Analysis Table'[Equity Contribution] )
    ),
    ALLSELECTED ( '_Bronn Portfolio Analysis Table'[Asset ID] ),
    FILTER (
        SUMMARIZE (
            '_Bronn Portfolio Analysis Table',
            '_Bronn Portfolio Analysis Table'[Count],
            '_Bronn Portfolio Analysis Table'[Project Term (mos)]
        ),
        '_Bronn Portfolio Analysis Table'[Count] = '_Bronn Portfolio Analysis Table'[Project Term (mos)] + 1
    )
)

 

  • The average of Cumulative IRR - Upside weighted by Equity Contribution is in green.
    • This expression makes sense as long as the final FILTER results in one row per Asset ID (appears to be the case)
  • I also rewrote the FILTER as FILTER ( SUMMARIZE (...) ), in order to apply a filter just on the required columns.
  • I'm not sure the ALLSELECTED is required, but it depends how you are using this measure so left it there.

 

As a side note, I would consider restructuring the data model so that you have an Asset table (one row per asset) related to the '_Bronn Portfolio Analysis Table'. This would avoid repeating common values across every row of a given Asset ID, and may simplify some of the DAX.

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mrothschild Member
Member

Re: SUMIFs/CALCULATE not doing what I need it to do

Thanks so much for your help - much appreciated.

 

Regarding restructuring the data, ironically, for years, I've avoided using pivot tables in excel, prefering INDEX to present pivoted data.  I just started using/learning BI a few weeks ago and flattened my matrix data to start the process.  I'm certain you're right about a better way to access the data and make programming and auditing more functional and elegant, but I'm not quite understanding the relationship functionality across different tables yet.