3 Replies Latest reply on Aug 21, 2018 2:23 AM by pavan kumar

    Add Measure values to itself

    pavan kumar

      Hi All,

       

      This is the data i have,

       

      Capture.PNG

      My requirement is

      I need to show

      Term1 AMount*.9 + Term2 Amount in place of Term 1

      Term1 Amount*.5*3+Term2 Amount *0 in place of Term 2

      Below is the calculated field(Renewals) i am using , unfortunately its not giving  me required output.

       

      TCapture1.PNG

      Plz find attached workbook.

       

       

      Thanks

      Pavan Kumar

        • 1. Re: Add Measure values to itself
          Naveen B

          Hi Pavan,

           

          Check the below snapshot

           

           

          Changed the calculation little bit like this

           

           

          find the attached workbook

          Hope this helps kindly mark this answer as correct and helpful so that it will help others

           

          BR,

          NB

          1 of 1 people found this helpful
          • 2. Re: Add Measure values to itself
            pavan kumar

            Hi Naveen ,

             

            Thank you so much for the reply ,

            instead of Amount i need to do this on other measure ie Forecast.

            When i am trying to apply same formula on Forecast it is throwing an error.

            Could you please review it if possible.

            Capture2.PNG

            Forecast is aggregated field.

            Plz find attached workbook.

            • 3. Re: Add Measure values to itself
              pavan kumar

              Thanks for the support Naveen , finally found the Solution.

              This is the formula

               

              IF attr([Term]) = 1 THEN

               

               

              (IF attr(CONTAINS([Quarter],"Q4")) THEN

              (SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}))+

              (lookup(SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}),1)*.1)

              ELSE

              ((SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}))) +

              ((lookup(SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}),-1)*.5)) END)*.5+

              (IF attr(CONTAINS([Quarter],"Q4")) THEN

              (SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}))+

              (lookup(SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}),1)*.1)

              ELSE

              ((SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}))) +

              ((lookup(SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}),-1)*.5)) END)

              ELSEIF ATTR([Term]) = 2 THEN

              (IF attr(CONTAINS([Quarter],"Q4")) THEN

              (SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}))+

              (lookup(SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}),1)*.1)

              ELSE

              ((SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}))) +

              ((lookup(SUM({FIXED [Quarter]:sum(if [Term]=1 then [Amount] END)}),-1)*.5)) END)*.5*3+

              (IF attr(CONTAINS([Quarter],"Q4")) THEN

              (SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}))+

              (lookup(SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}),1)*.1)

              ELSE

              ((SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}))) +

              ((lookup(SUM({FIXED [Quarter]:sum(if [Term]=2 then [Amount] END)}),-1))) END)*0

               

              END