cancel
Showing results for
Search instead for
Did you mean:
Member

## 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

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

Accepted Solutions
Super User

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

@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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

2 REPLIES 2
Super User

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

@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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Member

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

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.