topic Re: Productx with measure in DAX Commands and Tips

Productx with measure

McShock — Sun, 21 Apr 2024 08:27:35 GMT

Hi Dear DAX Experts, I know the almost excactly the same question has been asked before by sgupta22 but the solution was presented in the BI File and the file is no longer available. So let me ask for help for the following problem:

I want to compound monthly growth rates.

The growth rates are the result of a Measure:

So far so good, but now I want to compound theses growth rates by creating an Index showing the cumulative growth and I do not know how to do it. If the rates were in a column I could use productx. But the rates are in a measure. In Excel what I need would be this:

Is there a way to do this?

Thanks in advance.

Markus

Re: Productx with measure

Greg_Deckler — Sun, 21 Apr 2024 13:08:26 GMT

McShock Try:

Measure = VAR __Date = MAX('Dates'[Date]) VAR __Table = SUMMARIZE( FILTER( 'Dates', [Date] <= __Date), [Year], [Month], "__Value", [Performance %]) VAR __Result = PRODUCTX( __Table, [__Value] ) RETURN __Result

It's the measure aggregation pattern. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

If you need something more specific, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Productx with measure

sgupta22 — Mon, 22 Apr 2024 10:10:29 GMT

Hi Markus

I believe this is what you are looking for. Hopefully this solves your query!

Cheers

https://www.dropbox.com/scl/fi/sc3rk91amht8bt7gki47m/CAGR-calcualtion-v1.pbix?rlkey=rshoihhnaoufrkxjijj7o27sq&dl=0

Re: Productx with measure

McShock — Sun, 28 Apr 2024 16:54:38 GMT

Hi Greg, Thank you for taking the time!

This is interesting. Dax accepts the productx in this context, but yields me the monthly return that I already have instead of compounding it.

Thank you anyway.

Markus