Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mrothschild
Continued Contributor
Continued Contributor

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
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

@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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.