SUMIFs/CALCULATE not doing what I need it to do

[mrothschild]

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

[OwenAuger · Accepted solution]

@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

[mrothschild]

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.