- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- SUMIFs/CALCULATE not doing what I need it to do

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

mrothschild

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019
10:12 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019
11:26 AM

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

Proud to be a Datanaut!

2 REPLIES 2

OwenAuger

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019
11:26 AM

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

Proud to be a Datanaut!

mrothschild

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019
11:56 AM

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.