Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
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 ) )
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
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 ) )
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
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |