Calculate product by year

[Jkaelin]

Good morning,

Overall, I am seeking to produce a measure that can calculate the product by year.  All my current attempts have failed.  An overview is this:  I have a column of data in which I calculate the top 20% & bottom 20% via a percentile measure.  I then calculate the spread between those two.  The spread is summarized monthly.  

My hope is to take the spread results that are summarized by month & calculate the results on a yearly basis via "product", not sum or average, etc.  

My spread measure summarized for each month is this:

P/E Tk minus Bk:=IF (1 + ( ( [P/E Percentile Top k] - [P/E Percentile Bottom k] )
/ 100 ) 
= 1,
BLANK (),
1 + ( ( [P/E Percentile Top k] - [P/E Percentile Bottom k] )
/ 100 )
)

A snap shot of the summarized results are below.  I want similar results, but by year & the monthly results shown in the image to be multiplied together.  Any suggestions or ideas?  Kindly & Thank you!  James

[Phil_Seamark]

Hi @Jkaelin

Based on your sample data, can you please provide what the expected outcome should look like?

Feel free to mock up in Excel or similar to help clarify what you are after. 🙂

[Jkaelin]

@Phil_Seamark

Hey Phil!  Below is a sample data & expected outcome.  The expected outcome is highlighted in yellow.  

• The yellow formula is (like in Excel) =Product(IF(Year(Row Labels=2006),Column B)).  
• It's the 2006 data, multiplied together.  
• Also, 1.0165 = (1.02 X 1.00 X 0.98 X 1.01)
• Quick Note:  Column B is not a column of regular data or a calculated column, it's the pivot table result of measures.  

 

I was hoping a calculated(productx(...)) would work, but to no avail.  I've tried summarize & other alternatives.  I'm just not that good in DAX yet.  Any help would be much appreciated.

Thank you & kindly,

James