Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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. 

 

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
Memorable Member
Memorable Member

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors