Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |