OBIEE - average of 5 years & year over year growth/decline

3427784

I apologize in advance if this question has already been answered but could not find relevant information pertaining to my specific question.

I have a facts table that contain revenues for years 2000-2017. I am working on a treemap/heatmap to show revenues over all states and would like to also throw in another measure to show the 5 year average revenues and the year over year revenue growth.

Question - after some digging around I found that it may be best to create a separate column in in the RPD and doing the calculations in there via sql. I have also tried using avg(aggregate(REV by YEAR)) with no success. I was wondering if there is anything I can do on the front end to achieve the answers I am looking for.

Thank you in advance.