cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TeunVerhagen
Frequent Visitor

Formula for total calculation

Hi all,

 

I am having an issue, tried a lot, but cannot get a solution. This is the situation:

 

There are two tables.

 

- One table with Investments and periods, columns: InvestmentID - Start date - End Date - Start value - End value

- One table with Cashflow looking like this: InvestmentID - Cashflow type - Value - Start Date - End Date

 

These two tables are linked so that I can create the following formula:

(SUM(end value) - SUM(start value) - (Cashflow values) / SUM(start value) + SUM(Weighted Cashflow values)

 

So far so good. This formula calculates the return per investment per period. The next step is linking de returns to get a time weighted return. This is done by adding 1 to the returns, take the product of returns +1, and lastly minus 1. I made this formula: PRODUCTX(Table, (Return+1))-1.

 

This also works fine but only on investment level. When I want to calculate the time weighted return of multiple investments it goes wrong because my formula takes the product of all the returns in stead of the subtotals. Example:

 

Investment 1

Q1: 2%

Q2: 3%

Q3: 1.5%

Q4: 5%

 

Time weighted return = (1.02 * 1.03 * 1.015 * 1.05) -1

 

Investment 2

Q1: 1.5%

Q2: 2%

Q3: 4%

Q4: 3%

 

Time weighted return = (1.015 * 1.02 * 1.04 * 1.03) -1

 

But when I want to calculate the time weighted return of both investments this is what needs to happen:

 

Result when I create a table with a filter on 2 investments: (Calculation of return works fine for multiple investments)

Investment 1&2

Q1: 3%

Q2: 1.6%

Q3: 2.8%

Q4: 3.8%

 

Time weighted return: (1.03 * 1.016 * 1.028 * 1.038) -1 

 

But when I use this formula: PRODUCTX(Table, (Return+1))-1, PowerBI multiplies all individual returns of investment 1 and 2. What is the result? (1.02 * 1.03 * 1.015 * 1.05 * 1.015 * 1.02 * 1.04 * 1.03) -1. This is the wrong result.

 

Does anyone have an idea how to solve this? Below I posted a screenshot to visualize things; the outcome of the desired formula should be: (1.0278 * 1.0158 * 1.0167 * 1.1053) -1 = 17.32%

Knipsel.JPG

Can someone help me? I am stuck now for 3 days on this issue.

 

 

 

 

1 ACCEPTED SOLUTION
stevedep
Super User I
Super User I

Hi @TeunVerhagen ,

 

In this case its required to set the right level of aggregation in the productx command. Productx takes a table as a parameter. By giving a table on the level of period you will get the right results.

Modified Dietz = PRODUCTX(SUMMARIZE('NAV Dietz Table','NAV Dietz Table'[NAV Date Start],'NAV Dietz Table'[NAV Date End]),ROUND([Dietz]+1,2))-1

Kind regards, Steve. 

 

View solution in original post

4 REPLIES 4
stevedep
Super User I
Super User I

Hi @TeunVerhagen ,

 

In this case its required to set the right level of aggregation in the productx command. Productx takes a table as a parameter. By giving a table on the level of period you will get the right results.

Modified Dietz = PRODUCTX(SUMMARIZE('NAV Dietz Table','NAV Dietz Table'[NAV Date Start],'NAV Dietz Table'[NAV Date End]),ROUND([Dietz]+1,2))-1

Kind regards, Steve. 

 

View solution in original post

TeunVerhagen
Frequent Visitor

Thanks for your reply!

 

I have made an Excel with sample data and a PowerBI file but I can't share it in this forum for some reason.

 

Is there another way to send you the files?

 

Kind regards,

Teun

stevedep
Super User I
Super User I

Can you share some sample data of your tables with expected outcomes. Should be a quick fix.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors