3 Replies Latest reply on Oct 10, 2017 10:01 AM by Jim Dehner

    Creating a calculated field

    Ravi Chaturvedi

      Hi All,

       

      I have to create a calculated field wherein the original value is multiplied by a factor specific to criteria. PFB pivot table from my source file.

       

      if field criteria is 'A' then calculated field = original value * 0.25 and so on for criteria B, C and D. Request your support in writing the formula.

       

      RegionCriteriaOriginal valueCalculated field
      Asia PacificA22=C2*0.25
      B23=C3*0.5
      C24=C4*0.75
      D25=C4*0
      Central, North and South
        America
      A26=C2*0.25
      B27=C3*0.5
      C28=C4*0.75
      D29=C4*0
      Europe, Africa
        and the Middle East
      A30=C2*0.25
      B31=C3*0.5
      C32=C4*0.75
      D33=C4*0
      Grand Total 330
        • 1. Re: Creating a calculated field
          Deepak Rai

          {FIXED Region,Criteria, original value:SUM(IF Criteria="A" THEN (Original value*0.25) ELSEIF Criteria="B" THEN (Original value*0.50) ELSEIF Criteria="C" THEN (Original value*0.75) ELSE (Original value*0.0) END)}

           

          Try above and check for brackets. I just wrote it.

          Thanks

          Deepak

          • 2. Re: Creating a calculated field
            Alan Perez

            one example

            create calculated field for criteria

             

            Name Field: A

             

            (ORIGINAL VALUE) * .25

             

            CREATE OTHER CALCULATED FIEL

             

            IF region = 'Asia Pacific' then

             

            A (hera your calculate field, this case is A)

             

            and so you must create field by criteria

            • 3. Re: Creating a calculated field
              Jim Dehner

              Hi

              this is wherre a Case formul make sense

               

              Case [Criteria]

              When "A" then [Original Value] * .25

              When "B" then [Original Value] * .5

              When "C" then [Original Value] * .75

              When "D" then [Original Value] * 0

              End

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.