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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sgupta22
Helper II
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).

 

sgupta22_0-1683723370825.png

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.

 

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

 

Regards

Shashank

 

@Ashish_Mathur 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Great thanks again 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi please add the solution not only in the PBIX but also to a comment because over time you no longer have that PBIX available for download so the solution here isn't available rendering this post worthless.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors