March 29, 2023

Hello,

I have some data that shows percentage gains by date and I'd like to summarize it in a pivot table to show compounded gains by month.

I've tried adding a calculated field using the formula PRODUCT( 1+ "field name") -1 but this gives the same value as summing the values.

Does anyone know if it's possible to do this within a pivot table?

Trusted Members

Moderators

January 31, 2022

March 29, 2023

Here's a table with some dates over 3 months with some percentages.

The right hand column in the table shows the compounded total at the end of each month using the formulas PRODUCT (1+B2:B21)-1 , PRODUCT (1+B2:B21)-1 and PRODUCT (1+B2:B21)-1

The 2nd column in the pivot table is a calculated field attempting to use the same formula but unfortunately it gives the same result as summing the percentages in column 1

Trusted Members

Moderators

January 31, 2022

See attached. You can't do that with a regular pivot table. You need Power Pivot / Data Model (DM).

I created a few extra columns in the DM and some measures that achieve what you asked for. I believe you want the Month-to-date Compounded %, though I also included a Year-to-date calculation.

Answers Post

March 29, 2023

Thank you Riny,

That looks just what I'm after.

I've not used power pivot or the data model before.

The actual data I am going to analyse will be imported from a folder via power query.

Do I put the data straight into the data model at that point, what are the steps?

Also, if I wanted to compound an actual amount say from 100 using these percentages, I assume that would follow a similar process?

I will go through Mynda's videos on power pivot and data model but if you have any advice re this it would be greatly appreciated.

Trusted Members

Moderators

January 31, 2022

March 29, 2023

Hello Riny,

I've tried to copy your example (see attached) and it went ok until I got to the compounded%YTD measure.

This produces an error and stops me from building a pivot table.

I get an error message saying I can't use the MAX function within the filter of the CALCULATE function.

I've noticed that there is also an error with your compounded%YTD measure and yet you were able to build a pivot table.

Any ideas?

Trusted Members

Moderators

January 31, 2022

March 29, 2023

Here are some photos of my screen. I couldn't get screenshot to work in excel for some reason.

The first is the pivot table error message. After clicking ok, I get the error icons in the fields list. The third photo is the data model screen.

I'm using microsoft 365. Are there any settings I should have enabled?

Trusted Members

Moderators

January 31, 2022

I found a post from 2019 on this very error message.

https://www.sqlgene.com/2019/0.....t-allowed/

The solution provided there is to wrap the true/false bit in the FILTER function. Added such a measure to the model. Does it work on your side as well? If so, you are perhaps not using the latest version of Excel.

March 29, 2023

Thank you again,

The FILTER solution worked.

However, I've ran an update but your original solution still produces an error.

It's a bit baffling, I guess I could try an uninstall/ re-install.

Otherwise, I'll have to try contacting Microsoft which might prove to be a painfull endeavour!

Trusted Members

Moderators

January 31, 2022

Trusted Members

Moderators

November 1, 2018

March 29, 2023

Hello again,

I'm now practicing with some data over 3 years.

The formulas, as they are, work properly on the first year of data but give the wrong results in the following years.

I'm assuming ths is because I need to filter by years. I've had a go at changing the formulas to add a year filter but I keep getting syntax errors.

I'm new to DAX and although I've watched a few videos on CALCULATE, FILTER and EARLIER, I'm not really sure what's going on.

Could you give a brief explanation of the Comp YTD and Comp MTD column formulas and what needs to be changed to give correct results for all years,

Thanks

Trusted Members

Moderators

January 31, 2022

Trusted Members

Moderators

November 1, 2018

If you create a date table, you can then use time intelligence functions. You can create a compounding measure (Compounded Percent) using:

=VAR CP =PRODUCTX(Table2, 1+Table2[%gain])

RETURN IF(ISBLANK(CP),BLANK(),CP-1)

then your other two measures are:

CompoundYTD

=IF(COUNTROWS(Table2)>0,TOTALYTD([Compounded Percent],'Calendar'[Date]),BLANK())

(the COUNTROWS is just to hide values for months where you don't have data)

CompoundMTD

=TOTALMTD([Compounded Percent],'Calendar'[Date])

The following users say thank you to Velouria for this useful post:

Riny van Eekelen1 Guest(s)