I have a requirement where I need to get Total amount as well as Average Amount for a given characteristic and Time period: Calmonth/Year 01/2002 - 12/2002. I just need one line/row report with 2 columns one for Total and another one for Average. I do not need to display a row for each month in the excel.
I can easily get the Total Amount but getting Average Amount is difficult as I am not displaying individul rows for each month. I also do not want to display over all results. Some months may or may not have amounts, so I need to average amounts based on number of non-zero amounts. I tried using "calculate result as average" of amount keyfigure property but it doesn't work.
Any help would be appreciated.