Forum Discussion
Productx with measure
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
3 Replies
- Greg_Deckler
Community Champion
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 __ResultIt'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.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
sgupta22Helper II
Hi Markus
I believe this is what you are looking for. Hopefully this solves your query!
Cheers
SG
