8 Replies Latest reply: Jul 27, 2015 11:56 PM by Rod Jager RSS

    How to find the average of sales by Year, Quarter, Month excluding the Years/Quarters/Month that have "0" sales amount?

      I have a Bar Chart which contains one Measure ( SUM(SalesAMount) ) and one "drill down hierarchy" Dimension called "Date" which has (Year>Quarter>Month). I want to add a reference line which acts a Average of all sales.


      Now I need it in such a way that when I go down the hierarchy, it should give that corresponding Average. Additionally there are some years that have zero SalesAmount. I do not wish to have them included in my average.For example : If I press Year, then the reference line should be "Average of all sales by Year" & it should not include the years that have "0" SalesAmount. Now when I press on the Quarter, then the reference line should be "Average of all sales by Quarter" & so on.


      Below is what my Chart looks like now. I have presently manipulated the Reference line expression.





      I will be really grateful, if anybody could help me with this as soon as they can. Thank you.