5 Replies Latest reply: Aug 14, 2018 5:04 AM by Andrea Gigliotti RSS

    Average help

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have a bar chart with Month as dimension and Sum(Sales) as measure. But these sales are YTD values so for getting each month sales I have used below expression for calculation 2017 Sales.

       

      I have calculated 2017 Sales by using below expression getting the result on this bar chart.

      RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))


      Requirement:

      In a Reference line I have to show avg of 2017 Sales. Please help me how to write the avg of 2017 Sales expression.



       

       

      Thanks in advance



        • Re: Average help
          Andrea Gigliotti

          maybe this:

          Avg( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 1000000

           

          i hope it helps.

            • Re: Average help
              Mahitha M

              Hi Andrea,

               

              Thanks for your reply.

              I am getting 0.09M in the avg reference line by the above expression.

              I think I need to get 5.04 as average.

              I have calculated like below in manual process

              sum of all the months/12=4.87+5.66+4.73+4.96+4.15+5.94+6.24+5.71+6.07+6.38+ 5.88/12

              =60.59/12=5.04

               

              Please let me know is this correct process to validate the results.

                • Re: Average help
                  Andrea Gigliotti

                  ok so let's try the below expression:

                  Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 12

                    • Re: Average help
                      Mahitha M

                      Hi Andrea,

                      Still not working. I am getting 35M as average.

                      When its Sum( {< Year = {"$(=Year(Max(Date))-1)"}, Month = {"*"} >} Sales ) / 12 then it will take ytd Values but the above chart bar values as individual month values from YTD values those calculated by the below expression.


                      RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))