cancel
Showing results for
Did you mean:
Helper III

## Calculating Product of Instrument Contributions

I'm trying to calculate the contribution of my underlying holdings to my portfolio return.

I'm struggling to get my measures to reference a value for the previous day for the specific instrument in the current row.

Contributions PBIX File

This excel file explains what result I'm looking for. Excel

For each day I need:

PriceContribution = (Price % Change from previous Day)* (Instrument % of fund of previous Day)

Cumulative Contribution = ProductX(Contribution) per instrument per fund for the total date range

6 REPLIES 6
Moderator

## Re: Calculating Product of Instrument Contributions

@Johnsnowlife,

Could you please share the Excel file for me to download? I would check that what formula you use to calculate the Product of Contr+1. I guess that you create columns to calculate Pricechg, Contr and other fields in Excel, but in Power BI Desktop, you use measures instead, which give you different result.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper III

## Re: Calculating Product of Instrument Contributions

Hi Lydia

There is a link to the Excel file in the original post. In the Excel File I have used columns and a pivot table. But in the PBI file I want to use measures for eveything.

Moderator

## Re: Calculating Product of Instrument Contributions

@Johnsnowlife,

I am not able to edit the Excel file and I can't view the formulas you use to create these fields in Excel.

Since you get different values for calculated fields of Excel and measures of Power BI Desktop, I am confused about your expected result. Thus, could you please share the Excel file so that I can download rather than viewing data in Excel Online? And please check if the following measure returns your expected result in Power BI.

TotalContr = CALCULATE(productx(VALUES(DataAllFunds[Date]),1+[PriceContr]))-1

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III

Helper III

## Re: Calculating Product of Instrument Contributions

I used the below and I got my expected result for the product of the instrument contribution over my date period and calculated only for the last date.

```CumInstrContr =
VAR TDate =
CALCULATE ( MAX ( DataAllFunds[Date] ), ALL ( DataAllFunds ) )
RETURN
IF (
MAX(DataAllFunds[Date] ) = TDate,
CALCULATE(productx(values(DataAllFunds[Date]),1+[PriceContr]),ALLSELECTED(DataAllFunds[Date]))-1,blank())```

I don't really understand how the "values(DataAllFunds[Date]" part works though. Can you explain please?

Now I also want the sum of all the instrument price contributions per fund per day (FundDayContr). And then the product of (1+ FundDayContr) over all days in the period.

Helper III

## Re: Calculating Product of Instrument Contributions

For anyone interested in this, the following formulas ended up working. However I ran into trouble with these formulas using extensive RAM and I couldn't run them for periods more than 6 months on my actual table.

Can anyone advise on how I can optimise these?

```InstrPriceContr =
SUMX ( DataAllFunds, [PriceChg] * [InstrPctPrevDay] )

CumInstrContr =
VAR startDate =
CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ('Calendar'[Date] ) )
RETURN
if(min(DataAllFunds[Date])<startDate,blank(),CALCULATE (
PRODUCTX ( DataAllFunds, 1 + [InstrPriceContr] ),
ALLEXCEPT ( DataAllFunds, DataAllFunds[Fund], DataAllFunds[InstrCode] ),
DataAllFunds[Date] >= startDate
)
- 1
)

ProductT =
VAR startDate =
CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ('Calendar'[Date] ) )
VAR DateT = CALCULATE(max('Calendar'[Date]),ALLSELECTED('Calendar'[Date]))
RETURN
if(max(DataAllFunds[Date])<DateT,blank(),CALCULATE(PRODUCTX(SUMMARIZE(DataAllFunds,
DataAllFunds[Date],
DataAllFunds[Fund],
"SumInstrContrib",
sumx(DataAllFunds,[InstrPriceContr])),1+[SumInstrContrib])-1,ALLSELECTED(DataAllFunds[Date])))```

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors