2 Replies Latest reply on Feb 9, 2019 5:37 AM by Zhouyi Zhang

    Calculating Total Pipeline Amount on Status and Stage

    Lachlan Paull

      Hi,

      I am trying to calculate the sum on Opportunity Amount based upon only a select number of values from Stage and only a select number of values from Status.

       

      I want to create a total value for Early Negotiation (Stages = Identification or Qualification, Status' = Qualified or Unqualified) and;

       

      one for Advanced Negotiation (Stages = Agreement or Proposal, Status' = Qualified, Response Pending, Unqualified). Below is a screenshot of Early Negotiation for example.

       

      I've tried creating these as sets but want to include options for linking Identification and Qualified which in the screenshot below does not have any data currently in there. Just want these options to work once data is refreshed and these fields all have data. I've tried looking online but couldn't find much on pipeline opportunities like this. Thanks!

       

        • 1. Re: Calculating Total Pipeline Amount on Status and Stage
          Jim Dehner

          This sounds really interesting and would like to help you out - please post your twbx workbook so we can see your progress and where you had your problem

           

          Thanks

          Jim

          • 2. Re: Calculating Total Pipeline Amount on Status and Stage
            Zhouyi Zhang

            Hi, Lachlan

            Without seeing a sample  workbook, it is always difficult to provide a solution, and not sure what's your expected result and format as well, but try below calculation fields for Early Negotiation and Advance Negotiation

             

            [Early Negotiation]

            sum(if [Stage] = 'Identification' and ([Opportunity Status]='Qualified' or [Opportunity Status] = 'Unqualified' ) then [Amount] end)

            +

            sum(if [Stage] = 'Qualification' and ([Opportunity Status]='Qualified' or [Opportunity Status] = 'Unqualified' ) then [Amount] end)

             

            [Advanced Negotiation]

            sum(if [Stage] = 'Agreement' and ([Opportunity Status]='Qualified' or [Opportunity Status] = 'Unqualified' or [Opportunity Status] = 'Response Pending') then [Amount] end)

            +

            sum(if [Stage] = 'Proposal' and ([Opportunity Status]='Qualified' or [Opportunity Status] = 'Unqualified' or [Opportunity Status] = 'Response Pending') then [Amount] end)

             

            Hope this helps

             

            ZZ

            1 of 1 people found this helpful