cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

## ProductX not working with measure (alternative)

Hi everyone,

I currently have a measure which computes the year over year growth rate (see below).

I want to use these yearly growth rates to compute an index that starts at 10 and then multiplies the yearly growth rates sequentially.  For example, the value for 2018 should be 10*(1+10.0%)*(1+18.2%)*(1+7.7%) = 14.

I know that ProductX function is supposed to achieve a similar result, however, it doesn't seem to work on calculated measures (I can't have the growth as a calculated column).  Would be great if someone can suggest an alternative.  I am also providing a link to the file for reference.

Regards

Shashank

2 ACCEPTED SOLUTIONS
Super User

hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

This is great thanks!

Just one follow up, I tried to compute the CAGR using the following formula, but I am not getting the right result:

``````CAGR =
VAR BeginningDate = CALCULATE(MIN('Calendar'[Year]), ALLSELECTED('Calendar'[Year]))
VAR EndingDate = CALCULATE(MAX('Calendar'[Year]), ALLSELECTED('Calendar'[Year]))
VAR BeginningValue = CALCULATE([Measure], FILTER('Calendar', 'Calendar'[Year] = BeginningDate))
VAR EndingValue = CALCULATE([Measure], FILTER('Calendar', 'Calendar'[Year] = EndingDate))
VAR Years = (EndingDate - BeginningDate)
RETURN
IF(BeginningValue <= 0, BLANK(), (EndingValue / BeginningValue)^(1/Years) - 1)``````

Any idea why this formula is not working. Also the link to the file below:

https://www.dropbox.com/s/1tg4a8mj0dueypx/CAGR%20calcualtion.pbix?dl=0

Thanks a ton!

Regards

Shashank

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Great thanks again 🙂

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors